CODEWITHSUNDEEP
excelpowerquery
Rafael Osipov
Rafael Osipov

Reputation: 740

Group by - Excel Power Query multiple rows into one

I have data I loaded to Power Query - each Employee has mulitple rows and its looks like this:

empNum reason 1 reason 2 reason 3

3453 1 null null

3453 null 1 null

3453 null null 1

How can I transform the data to look like this?

empNum reason 1 reason 2 reason 3

3453 1 1 1

Upvotes: 0

Views: 510

Answers (2)

horseyride
horseyride

Reputation: 21298

In powerquery you can try

  • click select first column
  • right click ... unpivot other columns
  • click select attribute column, transform ... pivot column, use Values as values column, advanced options, dont aggregate 
    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"empNum"}, "Attribute", "Value"),
#"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Attribute]), "Attribute", "Value")
in #"Pivoted Column"

    Upvotes: 1

    • Mayukh Bhattacharya
    Mayukh Bhattacharya

    Reputation: 27233

    • EDIT

    You may try in this way as well, so, the steps are as follows:

    • Select any cell of the source,

    • From Data Tab --> Click From Table/Range (Under Get & Transform Group)

    • Select the First Column i.e. EmpNum and right click Unpivot Other Columns,

    • From Home Tab --> Under Transform Group --> Click Group By,

    • GroupBy EmpNum --> New Column Name -> Group --> Operation --> All Rows --> Press Ok

    • From Add Custom Tab --> Under General Group --> Click --> Custom Column

    • New Column Name --> Reason

    • Custom Column Formula -->

    `Table.Column([Group],"Value")`

    • Press Ok

    • Then Extract The Values From The Reason Column By Pressing The Dropdown and Click Extract Values

    • Select Delimiter as your choice ( I took comma )

    • Once Extracted Goto Home Tab --> Select Split Column by,

    • By Delimiter -> Delimiter is Comma should be at Each Occurence of the delimiter and press Ok (Also Note in Advanced Options By Default it is Split into Columns)

    • Remove the column that not required and from Home Tab press Close & Load Drop down, from Import Data choose either Existing sheet or New Sheet and press Ok

    let
    Source = Excel.CurrentWorkbook(){[Name="Table13"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"EmpNum"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"EmpNum"}, {{"Group", each _, type table [EmpNum=number, Attribute=text, Value=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Reason", each Table.Column([Group],"Value")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Reason", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Reason", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Reason.1", "Reason.2", "Reason.3"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Group"})
in
    #"Removed Columns"

    GROUP BY

    Upvotes: 1

    Related Questions