Forum Discussion

eacy's avatar
eacy
Icon for Helper II rankHelper II
7 years ago
Solved

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 th...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    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

  • eacy's avatar
    eacy
    7 years ago

    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

PattemManohar's avatar
PattemManohar
Icon for Community Champion rankCommunity Champion
7 years ago

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

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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

    • eacy's avatar
      eacy
      Icon for Helper II rankHelper II
      7 years ago

      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

    • eacy's avatar
      eacy
      Icon for Helper II rankHelper II
      7 years ago

      Hi Zubair_Muhammad 

       

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

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Icon for Community Champion rankCommunity Champion
        7 years ago

        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

  • eacy's avatar
    eacy
    Icon for Helper II rankHelper II
    7 years ago

    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's avatar
      PattemManohar
      Icon for Community Champion rankCommunity Champion
      7 years ago

      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.