Notifications
Clear all
Power Query
2
Posts
2
Users
0
Reactions
137
Views
Topic starter
Hello All,
I'm trying to transform data from one view to another (with Pivot, Unpivot, transpose, combine, etc.), but still stuck. Please see attached excel spreadsheet for more details.
In the tab Source - raw data that I get from the database; In the tab Outcome - what I need to get.
I tried PowerQuery and Pivot tables, but what I get at the end is value calculations instead of values from the column REV or just empty table.
If anyone had this challenge before, I would appreciate a hint so much.
Thank you
Cheers
Alex
Posted : 26/03/2021 11:35 pm
Hi Alex,
The Outcome sheet is not realistic, in a table there cannot be multiple columns with the same name.
See the file attached, the result is achieved with 2 different solutions.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"DN", type text}, {"STS", type text}, {"REV", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"DN"}, {{"Group", each _, type table [DN=nullable text, STS=nullable text, REV=nullable text]}}),
Transpose = Table.TransformColumns(#"Grouped Rows",{{"Group", each Table.PromoteHeaders(Table.Transpose(Table.RemoveColumns(_,{"DN"})), [PromoteAllScalars=true])}}),
#"Expanded Custom" = Table.ExpandTableColumn(Transpose, "Group", Table.ColumnNames(Table.Combine(Transpose[Group])), Table.ColumnNames(Table.Combine(Transpose[Group])))
in
#"Expanded Custom"
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"DN", type text}, {"STS", type text}, {"REV", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"DN"}, {{"Group", each _, type table [DN=nullable text, STS=nullable text, REV=nullable text]}}),
Transpose = Table.TransformColumns(#"Grouped Rows",{{"Group", each Table.PromoteHeaders(Table.Transpose(Table.RemoveColumns(_,{"DN"})), [PromoteAllScalars=true])}}),
#"Expanded Custom" = Table.ExpandTableColumn(Transpose, "Group", Table.ColumnNames(Table.Combine(Transpose[Group])), Table.ColumnNames(Table.Combine(Transpose[Group])))
in
#"Expanded Custom"
Posted : 27/03/2021 2:30 am
