Forum Discussion
renatopnovaes
5 years agoHelper I
How Remove duplicates value according other column values
Hi, I have the following table with the code column and product group. Product Group CODE A 100 A 101 B 102 B 103 C 101 C 104 In group A and C, I have the same co...
- 5 years ago
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MFCK1YGxDcFsJzDbCIltDGY7I6mBsE2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product Group" = _t, CODE = _t]), #"Grouped Rows" = Table.Group(Source, {"Product Group"}, {{"Value", each _}}), #"Renamed Columns" = Table.RenameColumns(#"Grouped Rows",{{"Product Group", "Name"}}), #"Split Tables" = Record.FromTable(#"Renamed Columns"), #"Transformed C" = Table.SelectRows(#"Split Tables"[C], let Code_A = #"Split Tables"[A][CODE] in each not List.Contains(Code_A, [CODE])), Custom = Table.Combine(Record.ToList(Record.TransformFields(#"Split Tables", {"C", each #"Transformed C"}))) in Custom - 5 years ago
Place the following M code in a blank query to see the steps. Grouped Rows is the important step
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MFCK1YGxDcFsJzDbCIltDGY7I6mBsE2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product Group" = _t, CODE = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product Group", type text}, {"CODE", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"CODE"}, {{"Product Group", each if List.Count(List.Intersect({[Product Group], {"A", "C"}})) = 2 then List.Select([Product Group], each _ <> "C") else [Product Group] }}), #"Expanded Product Group" = Table.ExpandListColumn(#"Grouped Rows", "Product Group"), #"Reordered Columns" = Table.ReorderColumns(#"Expanded Product Group",{"Product Group", "CODE"}), #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Product Group", type text}}) in #"Changed Type1"Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
AlB
5 years agoCommunity Champion
Place the following M code in a blank query to see the steps. Grouped Rows is the important step
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MFCK1YGxDcFsJzDbCIltDGY7I6mBsE2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product Group" = _t, CODE = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Product Group", type text}, {"CODE", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"CODE"}, {{"Product Group", each if List.Count(List.Intersect({[Product Group], {"A", "C"}})) = 2 then List.Select([Product Group], each _ <> "C") else [Product Group] }}),
#"Expanded Product Group" = Table.ExpandListColumn(#"Grouped Rows", "Product Group"),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded Product Group",{"Product Group", "CODE"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Product Group", type text}})
in
#"Changed Type1"
|
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |