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
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
Solved! Go to Solution.
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
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
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
@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
Proud to be a PBI Community Champion
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.
Proud to be a PBI Community Champion
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
Hi I am not able to change Schema name via parameter. Could you please look in to my original post and help me out?
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
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.
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 @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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
90 | |
83 | |
76 | |
64 |
User | Count |
---|---|
136 | |
111 | |
98 | |
97 | |
94 |