Product:

Microsoft SQL azure

Issue:

In a view, we want to exclude all text in column “konto” after character ” – ”

Solution:

In the select statement use something like this:

IIF (SUBSTRING ([Konto],1, CHARINDEX (' - ',[Konto])) ='', [Konto] ,SUBSTRING ([Konto],1, CHARINDEX (' - ',[Konto]))) as [Konto]

Substring extract text in column konto from first position to where we find the – character, this position is given with charindex, and then we check if the result is empty. In case the string does not contain a -, then we get a empty value. Then we show the orginal value – otherwise we show the substring without all text after – in the field.

SUBSTRING(string, start, length)

CHARINDEX(substring, string, start)

IIF(condition, value_if_true, value_if_false)

More Information:

SUBSTRING, PATINDEX and CHARINDEX string functions in SQL queries

https://www.w3schools.com/sql/sql_isnull.asp

https://www.geeksforgeeks.org/sql/sql-statement-to-remove-part-of-a-string/

https://help.zebrabi.com/kb/power-bi/sort-a-hierarchy-by-custom-sort-logic/

https://www.w3schools.com/sql/func_sqlserver_substring.asp

https://www.w3schools.com/sql/func_sqlserver_iif.asp

Product:

Mirantis Docker

https://docs.mirantis.com/mcr/25.0/single/ 

Issue:

Try to install Mirantis (docker) software on new Windows 2022 server. Get error like:

.\install.ps1 : File D:\temp\install.ps1 cannot be loaded. The contents of file
D:\temp\install.ps1 might have been changed by an unauthorized user or process, because the hash of
the file does not match the hash stored in the digital signature. The script cannot run on the specified system. For
more information, run Get-Help about_Signing..
At line:1 char:1
+ .\install.ps1 -Offline
+ ~~~~~~~~~~~~~
+ CategoryInfo : SecurityError: (:) [], PSSecurityException
+ FullyQualifiedErrorId : UnauthorizedAccess

Suggested Solution:

Check that you have the same local on new server as on old server with powershell command:

GET-WinSystemLocale
LCID Name              DisplayName
---- ----              -----------
1033 en-US             English (United States)

Change the security level with a powershell command:

Set-ExecutionPolicy -Scope Process -ExecutionPolicy Bypass

This will give this response text:
Execution Policy Change
The execution policy helps protect you from scripts that you do not trust. Changing the execution policy might expose
you to the security risks described in the about_Execution_Policies help topic at
https:/go.microsoft.com/fwlink/?LinkID=135170. Do you want to change the execution policy?
[Y] Yes [A] Yes to All [N] No [L] No to All [S] Suspend [?] Help (default is “N”): Y

Run then this command to install mirantis docker:
 .\install.ps1 -Offline
Installing the containers feature. It is a prerequisite for containers on Windows and requires a reboot.
Using preloaded zip File D:\temp\Docker.zip for installing package Docker
Using preloaded zip File D:\temp\Containerd.zip for installing package Containerd
Installed Docker 25.0.7m3, build caad55120f4543209cf95baeb66ea33b675a771
Installed Containerd 1.6.36
Install/upgrade completed
WARNING: Your machine needs to be rebooted now. Installed packages will not work without reboot.

PS D:\temp>

More Information:

https://learn.microsoft.com/en-us/troubleshoot/windows-client/system-management-components/signed-powershell-script-fails-hash-mismatch 

To get docker to create the container files on folder d:\docker, you need to create a text file in folder C:\ProgramData\docker\config, that looks like this;

Product:

Microsoft SQL Azure database

Issue:

How in a view, list a value, instead of blank – in case the record have no value in that column?

Solution:

Use

COALESCE(NULLIF([columntocheck],”), [columntoshowinstead]) AS [nameofcolumninview]

Where the function check if equal to ”, and then replace with other value listed.

e,g, if columntocheck is ” we replace it with a value from column columntoshowinstead.

The View can be created with SQL like this:

CREATE VIEW [DBO].[KontoView]
------------------------------------------------------------
-- example, you need to adjust it to your table data
------------------------------------------------------------
AS

SELECT  [key_Konto] as 'Konto_KEY'
     ,[Typ]
     ,[Konto]
     ,[Konto_Text]
     ,[Konto_Parent]
     ,[Konto] + ' ' + [Typ] + ' ' + [Konto_Parent] AS 'Konto_with_parent'
     ,[Area]
     , COALESCE(NULLIF([konto_uniq],''), [KONTO]) AS [Konto_Uniq]
    -- ,[startDate]
    -- ,[endDate]
    -- ,[IsActive]
FROM [DBO].[KontoTable]
WHERE [IsActive] = 'Y'

More information:

https://blog.devart.com/null-vs-empty-values-in-sql-server.html

https://www.w3schools.com/sql/func_sqlserver_nullif.asp

SELECT COALESCE(NULLIF(SomeColumn,''), ReplacementColumn)
FROM SomeTable

https://bytescout.com/blog/coalesce-nullif-isnull-functions-in-sql.html
https://learn.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql?view=sql-server-ver17
https://www.w3schools.com/sql/func_sqlserver_coalesce.asp
https://www.w3schools.com/sql/sql_isnull.asp

The SQL Server ISNULL() function lets you return an alternative value when an expression is NULL:

or we can use the COALESCE() function, like this:

Product:
Microsoft Power BI Desktop

Issue:

Get error when create a path / Hierarki function in Power BI.

Try to follow this instructions:  https://youtu.be/iwRqSl-_zvU?si=W0BTbtLd071_EiKD

Solution:

Error like: Each value in ‘Hierarki'[Konto] must have the same value in ‘Hierarki'[Konto_Parent]. The value ‘10000’ has multiple values.

When Konto is the child column, and Konto_Parent is the parent column. The PATH() function requires a strict one-parent-per-child hierarchy.

The part of the table that you import into the Power BI table, to build a Hierarki on, need to only have the lowest item ‘10000’ once in the ‘Konto’ column.

In the creation of the table, you need to filter better so you only get ONE item on each row;

Hierarki =

FILTER (

    'ResultKonto',

    'ResultKonto'[Typ] = "Intern RR" && 'ResultKonto'[area] = "xxx"

        && NOT 'ResultKonto'[Konto_KEY] IN {233, 250, 251, 252}

)

The solution code depends on your data, but try to work with && ‘ResultKonto'[area] = “xxx”  to narrow the selection of data in your table.

Please also do not use hard coded KEY/ROWs in the selection, as this will be affected when the data is updated, and then maybe the wrong part is not included.

The value in the parent column “konto_parent” must exist in the child column “konto” – if it does not, you get a error like:

The value ‘xxx’ in ‘Hierarki'[Konto_Parent] must also exist in ‘Hierarki'[Konto]. Please add the missing data and try again.

More Information:

https://www.daxpatterns.com/parent-child-hierarchies/ 

https://dax.guide/path/ 

https://simplebiinsights.com/power-bi-path-function-for-parent-child-hierarchies-in-dax/ 

Product:

Microsoft Power BI desktop

Issue:

How add a custom column with two text strings together?

Solution:

There are more than one why to do this, in the Transform data, you can add code like this:

Period = Table.AddColumn( #"Renamed Columns", "År - Månad", each Number.ToText ( [Year]) & " - " & Number.ToText ( [Month] ))

Where the #”Renamed columns” is the name of the previus step in the transformation.
“År – Månad” is the new name of the new column.
As the column YEAR is a integer, we need to convert it to text before we can use the & to put several text/string fields together.
Number.ToText only work in Power Query M.

In the report table tool you can use the & ” – ” & to put together two columns into a new column, as shown above.

Period = 'Val av Period'[År] & " - " & FORMAT ('Val av Period'[Månadsnr], "0#" )

Use FORMAT to get it as two digits in the month part.

To get the 3 left character of month name in a column called Month name;

Month Short = UPPER ( LEFT ( ‘calender table’ [Month Name] , 3 )

To get leading zeros on month, use code like this:

Month Number  =  Table.AddColumn(#”Renamed Columns”, “Number to Text”, each  Number.ToText([Month] , “D2” ))

The “D2″ second parameter, tells that there should be two characters, and fill up with zero in result.

In Power Query, you can convert month column to show two digit numbers with;

Month Number  =  Text.PadStart(Text.From([Month]),2,”0″)

To convert a date column to only year and month use;

Period  =  Date.ToText([Date],”yyyy MMM”)

The CONCATENATE function joins two text values by directly appending the second string to the first, without any space or delimiter. Both input values must be of text data type. If numeric values are used, they will automatically be converted to text before concatenation.

New Column = CONCATENATE(“Hello”, “World”)  , can also be written as;

New Column = “Hello” & ” ” & “World” , to get a space between the words.

You can also write it similare to this:

New Column = CONCATENATE ( “Hello” , CONCATENATE  ( ” ” , “World” ) )

More Information:

https://lets-viz.com/blogs/dax-function-left/ 

https://blog.datumdiscovery.com/blog/read/extracting-text-made-easy-understanding-the-left-function-in-power-bi

https://lets-viz.com/blogs/dax-function-format/

https://lets-viz.com/blogs/dax-function-concatenate/ 

Microsoft Power BI with Desktop Training Course | Udemy

https://powerquery.how/number-totext/

https://datascientest.com/en/mastering-power-query-number-totext-a-comprehensive-tutorial-for-power-bi-users 

https://www.spguides.com/power-bi-convert-number-to-text/ 

Merge columns (Power Query) – Microsoft Support

https://www.fourmoo.com/2015/03/25/power-query-adding-leading-zero-to-number-eg-month-number/

https://www.notjustaprettydashboard.com/how-to-create-single-letter-month-day-name-columns-in-power-bi/

https://radacad.com/generate-year-month-day-combinations-in-power-bi-report-using-power-query-date-totext-function/