Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
eacy
Helper II
Helper II

Problem using the PATH function

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 
14112
314
113
133
1623
117162
221162
81162

 

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

 

BR

Esben

2 ACCEPTED SOLUTIONS

@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

PATH function.png


Regards
Zubair

Please try my custom visuals

View solution in original post

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

View solution in original post

12 REPLIES 12
v-yulgu-msft
Microsoft Employee
Microsoft Employee

Hi @eacy,

 

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

 

As using DAX funtion PATH would prompts such an error.

1.PNG

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
PattemManohar
Community Champion
Community Champion

@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

image.png





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




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

@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.





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




@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

PATH function.png


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

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

Hi @Zubair_Muhammad 

 

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

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


Regards
Zubair

Please try my custom visuals

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.

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

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

Regards
Zubair

Please try my custom visuals

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.