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

[kk1791]

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 ID	END ITEM	PARENT	COMPONENTS	DESIRED OUTPUT PATH
001	1200035		1200035	1200035
001	1200035	1200035	12035FRAME05	1200035/12035FRAME05
001	1200035	12035FRAME05	831203505	1200035/12035FRAME05/831203505
001	1200035	831203505	12035R-5	1200035/12035FRAME05/831203505/12035R-5
001	1200035	831203505	12035R1-2	1200035/12035FRAME05/831203505/12035R1-2
001	1200035	12035R1-2	RS3016-007	1200035/12035FRAME05/831203505/12035R1-2/RS3016-007
001	1200035	12035R-5	RS3016-007	1200035/12035FRAME05/831203505/12035R-5/RS3016-007

[amitchandak]

@kk1791 , Last two records have same component but different parent, that can the issue

[kk1791]

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]

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

[kk1791]

I tried your solution and it worked perfectly. Thank you for that. But when I try to run for all locations for the same End Item I am getting this error: The value '35SHRINK2' in 'BOM'[PARENT] must also exist in 'BOM'[NewComponents]. Please add the missing data and try again.

Could you please guide me on what should I do to fix this issue?

I am attaching the data link for reference: https://docs.google.com/spreadsheets/d/1Gzziln2BShPoVpeZm-ohKkcj_ctkPUBO/edit?usp=sharing&ouid=11116...

Thank you in advance