Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have a question regarding comma seperated values.
The example data consists out of three columns: "Product_ID" (unique identifier), "Parts_Used" (comma sperated list) and "Price" (simple number).
As you can see in the "Matching"-table I'd like to be able to change the part names by updating the corresponding names within this table. The problem is that I want to keep the comma seperated form of the "Parts_Used" column.
Is this somehow possible?
Here you can find the sample file:
Kind regards
Nilso
Solved! Go to Solution.
Hi @Nilson ,
You could try to use M code to achieve this goal. You could refer to my sample for details.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY9LCoAwDAWvIll30XzVW7gvRbyCeH+MrSmIu0dnmpeUAggJtuO89oxp6oEjmDMkFqipAA2RgmuExZl72kT+TcT4gU8bslATZYhrDJr9SYU718FHkX0b39UsRMpRFDfg2m5Qg1pv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Product_ID = _t, Parts_Used = _t, Price = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Product_ID", Int64.Type}, {"Parts_Used", type text}, {"Price", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Split([Parts_Used], ",")),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Replaced Value" = Table.ReplaceValue(#"Expanded Custom"," ","",Replacer.ReplaceText,{"Custom"}),
#"Merged Queries" = Table.NestedJoin(#"Replaced Value", {"Custom"}, #"Table (2)", {"Part_Old"}, "Table (2)", JoinKind.LeftOuter),
#"Expanded Table (2)" = Table.ExpandTableColumn(#"Merged Queries", "Table (2)", {"Part_New"}, {"Part_New"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Table (2)",{"Parts_Used", "Custom"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Product_ID"}, {{"Part_new", each Text.Combine([Part_New], ","), type text}, {"Price", each List.Average([Price]), type number}})
in
#"Grouped Rows"
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Nilson ,
It seems that you upload wrong sample, I didn't see "Product_ID" (unique identifier), "Parts_Used" (comma sperated list) and "Price" (simple number) in your link. So if possible could you please inform me more detailed information(such as your expected output and your sample data)? Then I will help you more correctly.
Please do mask sensitive data before uploading.
Thanks for your understanding and support.
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Nilson ,
You could try to use M code to achieve this goal. You could refer to my sample for details.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY9LCoAwDAWvIll30XzVW7gvRbyCeH+MrSmIu0dnmpeUAggJtuO89oxp6oEjmDMkFqipAA2RgmuExZl72kT+TcT4gU8bslATZYhrDJr9SYU718FHkX0b39UsRMpRFDfg2m5Qg1pv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Product_ID = _t, Parts_Used = _t, Price = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Product_ID", Int64.Type}, {"Parts_Used", type text}, {"Price", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Split([Parts_Used], ",")),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Replaced Value" = Table.ReplaceValue(#"Expanded Custom"," ","",Replacer.ReplaceText,{"Custom"}),
#"Merged Queries" = Table.NestedJoin(#"Replaced Value", {"Custom"}, #"Table (2)", {"Part_Old"}, "Table (2)", JoinKind.LeftOuter),
#"Expanded Table (2)" = Table.ExpandTableColumn(#"Merged Queries", "Table (2)", {"Part_New"}, {"Part_New"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Table (2)",{"Parts_Used", "Custom"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Product_ID"}, {{"Part_new", each Text.Combine([Part_New], ","), type text}, {"Price", each List.Average([Price]), type number}})
in
#"Grouped Rows"
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |