Forum Discussion
(M code)How to union and distinct multi column data then transformer to new column?
Hi QZ
I'm not sure this is the most efficient way but it is achievable without writing M:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIsKMhJBdLIbCMktrlSrE40WMS/KDEvPVXbUMEnNTc/TxskZAzEYB6QNkHSZArWhCxrDsROiXlACDQAapIFUMwSpkvbSAEqbwwxIBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SequenceNo = _t, Name1 = _t, Quantity1 = _t, Name2 = _t, Quantity2 = _t, Name3 = _t, Quantity3 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"SequenceNo", Int64.Type}, {"Name1", type text}, {"Quantity1", Int64.Type}, {"Name2", type text}, {"Quantity2", Int64.Type}, {"Name3", type text}, {"Quantity3", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"SequenceNo"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"Value", type text}}, "en-GB"), {{"Value", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Value"),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value", type text}}),
#"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type1", {{"Value", Splitter.SplitTextByDelimiter("+", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Value"),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Value", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type2", "Custom", each " " & [Value]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value"}),
#"Split Column by Character Transition" = Table.SplitColumn(#"Removed Columns", "Custom", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Custom.1", "Custom.2"}),
#"Changed Type3" = Table.Buffer( Table.TransformColumnTypes(#"Split Column by Character Transition",{{"Custom.2", Int64.Type}})),
#"Filled Up" = Table.FillUp(#"Changed Type3",{"Custom.2"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Custom.1] <> " ")),
#"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Attribute"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Custom.1]), "Custom.1", "Custom.2", List.Sum),
#"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{" Apple", " Orange", " Lemon", " Banana"})
in
#"Replaced Value"Hi QZ
Just copy the following code and past it in advanced editor. the steps can be summerized, but I tried to provide a simple solutions.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIsKMhJBdLIbCMktrlSrE40WMS/KDEvPVXLUMEnNTc/TwukwRiIwTwgbYKkyRSsCVnWHIidEvOAEGgA1CQLoJglTJeWkQJQHqzAGGJCLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [No = _t, Name1 = _t, Quantity1 = _t, Name2 = _t, Quantity2 = _t, Name3 = _t, Quantity3 = _t]), #"Merged Columns" = Table.CombineColumns(Source,{"Name1", "Quantity1", "Name2", "Quantity2", "Name3", "Quantity3"},each _,"Merged"), #"Added Custom" = Table.AddColumn(#"Merged Columns", "Custom", each List.Transform(List.Split([Merged],2),(x)=> if Text.Contains(x{0},"*") then Text.Split(x{0}," ") else {x{0}&"*"&Text.From(x{1})})), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each List.Combine([Custom])), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Merged", "Custom"}), #"Expanded Custom.1" = Table.ExpandListColumn(#"Removed Columns", "Custom.1"), #"Split Column by Delimiter" = Table.SplitColumn(#"Expanded Custom.1", "Custom.1", Splitter.SplitTextByDelimiter("*", QuoteStyle.Csv), {"Custom.1.1", "Custom.1.2"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"No", Int64.Type}, {"Custom.1.1", type text}, {"Custom.1.2", Int64.Type}}), #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Custom.1.1]), "Custom.1.1", "Custom.1.2", List.Sum) in #"Pivoted Column"
Hi QZ
Another solution
let
Source = YourSource,
Transform = Table.FromRows(
List.TransformMany(
Table.ToRows(Source),
each {1.. (List.Count(Table.ColumnNames(Source))-1)/2},
(x,y)=>{x{0}, Text.Split(x{2*y-1}, " "), x{2*y}}),
{"SequenceNo", "Name", "Quantity"}),
Expand = Table.ExpandListColumn(Transform, "Name"),
Quantity = Table.ReplaceValue(Expand, each Number.From(Text.AfterDelimiter([Name], "+")), null, (x,y,z)=> y??x, {"Quantity"}),
Name = Table.TransformColumns(Quantity, {{"Name", each Text.BeforeDelimiter(_, "+"), type text}}),
Pivot = Table.Pivot(Name, List.Distinct(Name[Name]), "Name", "Quantity", List.Sum)
in
Pivot
Stéphane
thank for your post.
