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
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"
In powerquery you can try
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"
