(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"