Problem using the PATH function

[eacy]

Hi

I would like to use the DAX path function on my dataset but currently, it doesn't comply with the rules of the path function.

My dataset is missing a row with an empty parent column for the top parent like it should according to the documentation (I am missing the row marked with red.

EmployeeKey ParentEmployeeKey

121
14	112
3	14
11	3
13	3
162	3
117	162
221	162
81	162

Can anybody explain to me how I can add this row in either DAX or Power Query?

BR

Esben

[Zubair_Muhammad · Accepted solution]

@eacy 

You can also directly add a PATH using "M"/Power Query.

Please see the attached file's Query Editor as well

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRR0lEyNDRSitWJVjIGsU3ATENDINsYwjRGMM2MEGxDc5ByM4hWIyNDJJ4FnBMLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [EmployeeKey = _t, ParentEmployeeKey = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"EmployeeKey", type text}, {"ParentEmployeeKey", type text}}),
     NewStep=Table.AddColumn(ChangedType, "Path", each let 
     myfunction=(myvalue)=>
                    let
                    mylist=Table.SelectRows(ChangedType,each [EmployeeKey]=myvalue)[ParentEmployeeKey],
                    result=Text.Combine(mylist)
                    in
                    if result= null or result ="" then "" else if @myfunction(result)=null or @myfunction(result)="" then result else result & "|" & @ myfunction(result)
in
Text.Combine(List.Reverse(List.RemoveItems({[EmployeeKey]}&{[ParentEmployeeKey]}&Text.Split(myfunction([ParentEmployeeKey]),"|"),{"",null})),"|"))
in
    NewStep