Combine/Merge multiple columns based on its name

[PBIDevNoob]

Hi!

I need a power query / M query that can combine multiple columns with similar names.

Let's say I have the following columns: 

I want to combine:

• "Start Date-Month_1" with "Start Date-Year_1" and name it "Start Date_1"
• "End Date-Month_1" with "End Date-Year_1" and name it "End Date_1"
• "Start Date-Month_2" with "Start Date-Year_2" and name it "Start Date_2"
• "End Date-Month_2" with "End Date-Year_2" and name it "End Date_2"

I have 100 more columns like these so I can't do them manually and need a query that can combine all of them in one go. How do I do this?

Thanks! 

[v-juanli-msft · Accepted solution]

Hi @PBIDevNoob 

In Edit queries, add an index column, click on the "index column", then select "unpivot other columns".

Add column->Extact->"Text After Delimiter"/"Text Before Delimiter"

#"Inserted Text After Delimiter" = Table.AddColumn(#"Unpivoted Other Columns", "Text After Delimiter", each Text.AfterDelimiter([Attribute], "_"), type text),
#"Inserted Text Before Delimiter" = Table.AddColumn(#"Inserted Text After Delimiter", "Text Before Delimiter", each Text.BeforeDelimiter([Attribute], "-"), type text),

Add column->Merge columns

Remove useless columns

To make the "start date" and "end date" as columns name, click on the column, select "pivot columns"

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Zubair_Muhammad]

@PBIDevNoob 

This can be one way. I used only 2 rows of data to test

Please see attached Excel file for steps

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date_Month_1", Int64.Type}, {"Start Date_Month_2", Int64.Type}, {"Start Date_Year_1", Int64.Type}, {"Start Date_Year_2", Int64.Type}, {"End Date_Month_1", Int64.Type}, {"End Date_Month_2", Int64.Type}, {"End Date_Year_1", Int64.Type}, {"End Date_Year_2", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns1", "Commons", each Text.BeforeDelimiter([Attribute], "_")&"_"&Text.AfterDelimiter([Attribute], "_", {0, RelativePosition.FromEnd})),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Commons", "Attribute", "Value"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Attribute"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Index", "Commons"}, {{"ALL", each _[Value]}}),
    #"Extracted Values" = Table.TransformColumns(#"Grouped Rows", {"ALL", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Pivoted Column" = Table.Pivot(#"Extracted Values", List.Distinct(#"Extracted Values"[Commons]), "Commons", "ALL"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns1"