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