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

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

12 Replies

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

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    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