Forum Discussion

kk1791's avatar
kk1791
Frequent Visitor
4 years ago

Path error: Each value in [COMPONENTS] must have the same value in [PARENT]

Hello,

I am trying to create a calculated column using the PATH function: Path = PATH(COMPONENTS, PARENT). I am getting the error "Each value in [COMPONENTS] must have the same value in [PARENT]. The value '001:1200035:RS3016-007' has multiple values."

Any tips on how to solve this issue? 

SITE IDEND ITEMPARENTCOMPONENTSDESIRED OUTPUT PATH
0011200035 12000351200035
0011200035120003512035FRAME051200035/12035FRAME05
001120003512035FRAME058312035051200035/12035FRAME05/831203505
001120003583120350512035R-51200035/12035FRAME05/831203505/12035R-5
001120003583120350512035R1-21200035/12035FRAME05/831203505/12035R1-2
001120003512035R1-2RS3016-0071200035/12035FRAME05/831203505/12035R1-2/RS3016-007
001120003512035R-5RS3016-0071200035/12035FRAME05/831203505/12035R-5/RS3016-007
Need Help

4 Replies

    • kk1791's avatar
      kk1791
      Frequent Visitor
      4 years ago

      Thank you for the response. My question is when there are multiple parents for components how to create a path? Any other options to overcome this?

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi kk1791 ,

        It seems that there is no good solution to handle with it. The possible method is "remove" the duplicate value for the field [COMPONENTS] to avoid this error...I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can follow the below steps to get it:

        1. Add new custom column in Power Query Editor 

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwVNJRMjQyMDAwNsXKitUhoMrY1C3I0dfVAKdShAIdJQtjsAAOxQhZqMYgXWIVGuoa4bYfLKujFBRsbGBopmtgYI5Hqa4pmspYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"SITE ID" = _t, #"END ITEM" = _t, PARENT = _t, COMPONENTS = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SITE ID", type text}, {"END ITEM", type text}, {"PARENT", type text}, {"COMPONENTS", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"COMPONENTS"}, {{"Count", each Table.RowCount(_), Int64.Type},{"Index", each Table.AddIndexColumn(_, "Index",1,1), type table}})//{{"Count", each Table.RowCount(_), Int64.Type},{"Index", each Table.AddIndexColumn(_, "Index",1,1)}, {"Details", each _, type table [SITE ID=nullable text, END ITEM=nullable text, PARENT=nullable text, COMPONENTS=nullable text]}})
,
    #"Expanded Index" = Table.ExpandTableColumn(#"Grouped Rows", "Index", {"SITE ID", "END ITEM", "PARENT", "Index"}, {"SITE ID", "END ITEM", "PARENT", "Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Index", "NewComponents", each if [Count]>1 then [COMPONENTS]&"_"&Text.From([Index]) else [COMPONENTS]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count", "Index"})
in
    #"Removed Columns"

        2. Create a calculated column as below to get the path

        Column = PATH('Site'[NewComponents],'Site'[PARENT])

        Best Regards