Forum Discussion

PBIDevNoob's avatar
PBIDevNoob
Helper I
7 years ago
Solved

Combine/Merge multiple columns based on its name

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! 

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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.

2 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    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's avatar
    Zubair_Muhammad
    Community Champion
    7 years ago

    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"