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...
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.

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"