Add Column query

Hi tgjones43 ,

Please check the following steps as below.

1. Merge column A and column B in Table 1.

2. Duplicate table1 and add index in the duplicated table. (Table (2)).

3.Merge the two tables to get the result as we need, in the merged table, we should do some operations to get the result as we need. Please check the M code for the three tables.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLMKcksKU1JBTIVlGJ1IILBOfkFqCIBpUUF+cUgMUOwmBE2rUYYWo1waEWIGYHFjLEZZ4xhnDEW44xxGIcQM1aKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Column B" = _t, #"Required Column" = _t]),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Required Column"}),
    #"Merged Columns" = Table.CombineColumns(#"Removed Columns",{"Column A", "Column B"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged")
in
    #"Merged Columns"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLMKcksKU1JBTIVlGJ1IILBOfkFqCIBpUUF+cUgMUOwmBE2rUYYWo1waEWIGYHFjLEZZ4xhnDEW44xxGIcQM1aKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Column B" = _t, #"Required Column" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column A", Int64.Type}, {"Column B", type text}, {"Required Column", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column B] = "Purpose")),
Partition = Table.Group(#"Filtered Rows", {"Column A"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Column B", "Index"}, {"Partition.Column B", "Partition.Index"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Expanded Partition", {{"Column A", type text}}, "en-US"),{"Column A", "Partition.Column B"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged")
in
    #"Merged Columns"

let
    Source = Table.NestedJoin(Table1,{"Merged"},#"Table1 (2)",{"Merged"},"Table1 (2)",JoinKind.LeftOuter),
    #"Expanded Table1 (2)" = Table.ExpandTableColumn(Source, "Table1 (2)", {"Partition.Index"}, {"Table1 (2).Partition.Index"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Expanded Table1 (2)", {{"Table1 (2).Partition.Index", type text}}, "en-US"),{"Table1 (2).Partition.Index", "Merged"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged.1"),
    #"Removed Duplicates" = Table.Distinct(#"Merged Columns"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Duplicates", "Merged.1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Merged.1.1", "Merged.1.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.1.1", Int64.Type}, {"Merged.1.2", type text}}),
    #"Split Column by Position" = Table.SplitColumn(#"Changed Type", "Merged.1.2", Splitter.SplitTextByPositions({0, 1}, false), {"Merged.1.2.1", "Merged.1.2.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Merged.1.2.1", Int64.Type}, {"Merged.1.2.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Merged.1.1", "Required Column"}, {"Merged.1.2.1", "Column A"}, {"Merged.1.2.2", "Column B"}})
in
    #"Renamed Columns"

For more details, please check the pbix as attached.

Regards,

Frank