(M code)How to union and distinct multi column data then transformer to new column?

[QZ]

The Name and Quantity columns each have three groups. Some cells are quite common, with just one fruit name in the Name column and the quantity of that fruit in the Quantity column. However, some cells are special: the Name cell contains multiple fruit names and quantities, stored as a fruit name * quantity followed by a space, then followed by another fruit name * quantity. The Quantity column then displays the total quantity for each fruit.
My purpose is to count the fruit name and total quantity for each row.
May I know how to achieve it? May I know the detail code? thanks!

[Omid_Motamedise]

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"

[slorin]

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 

[v-dineshya]

Hi @QZ ,

Thank you for reaching out to the Microsoft Community Forum.

Hi @SamWiseOwl , Thank you for your prompt response.

Hi @QZ , Could you please try the proposed solution shared by @SamWiseOwl . Please do let us know if you have any further queries.

Regards,

Dinesh

[SamWiseOwl]

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"