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

[v-yulgu-msft]

Hi @eacy,

Please refer to Zubair_Muhammad' suggestion which works in your scenario. 

As using DAX funtion PATH would prompts such an error.

It doesn't apply to your scenario, because "Values inparent_columnNamemust be present inID_columnName. That is, you cannot look up a parent if there is no value at the child level.", as mentioned in document.

Best regards,

Yuliana Gu

[PattemManohar]

@eacy I understand that you want to insert the top parent key to the existing dataset.
If you want to do this in DAX then Create a new table as below

Test200Out = 
VAR _TopParent = ROW("EmpKey",121,"ParentKey",BLANK())
RETURN UNION(_TopParent,Test200InsertVal)

In Power Query Editor, just click on the "Source" in applied steps and then you can edit or insert the dataset

[eacy]

Hi @PattemManohar 

Sorry for not being more precise about my problem.

In my case, I have thousands of parent/child relations and not only one as in my example.

This means that I need to figure out which one is a top parent before I can put it in the ROW command (I need a variable containing "121" and not hardcoded)

Beside that this approach is failing with "Each table argument of 'UNION' must have the same number of columns."

I expect that "Test200InserVal" is my current table which is currently missing the top parent, right?

BR

Esben

[PattemManohar]

@eacy Yes, that is the table which is missing your top parent in it. So you want to derive that dynamically... gotta you !!

Could you please let me know what you want as Top Parent, providing some appropriate test data and expected top parent key out of it.

[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

[Anonymous]

Hi I am not able to change Schema name via parameter. Could you please look in to my original post and help me out? 

https://community.powerbi.com/t5/Desktop/Schema-change-to-multiple-queries-SAP-hana-and-Direct-query...

Thanks

[eacy]

Hi @Zubair_Muhammad 

I marked the wrong comment as the solution. Do you know how to change that to your comment

[Zubair_Muhammad]

HI @eacy 

Could you share your file with me in which you are getting a stack overflow error.

I will try to fix it using some Buffer functions

[42]

Hello @Zubair_Muhammad ,

Did you solve the issue using buffer functions? I would also like to be able to create a column with a path using power query but I get serious peformance issue which I assume is due to that my table contains tens of thousands of rows.

My need is really to be able to filter out all records that is hierarchically ordered below one selected top parent.

[eacy]

Hi @Zubair_Muhammad 

The table contains 112377 rows so I am not sure how to give it to you. I have exported it and have it as a notepad file but I cannot attach it to this ticket. I would need to email it to you.

BR

Esben

[Zubair_Muhammad]

Hi Esben,
You can mail me at zubair@excelnaccess.com

[eacy · Accepted solution]

Hi @Zubair_Muhammad ,

This is perfect, but it seems to have one flaw. If the table is too big I get this error

Expression.Error: Evaluation resulted in a stack overflow and cannot continue.

When I limit the table it works fine, but if you know what causes the error I would like to know 🙂

BR

Esben