Forum Discussion
Unpivot Multiple Sections of Data
Anonymous - Not exactly pretty, essentially two queries and a merge, but at least only one query...
let
Source1 = Csv.Document(File.Contents("C:\temp\powerbi\pivot.csv"),[Delimiter=",", Encoding=1252]),
Source = Csv.Document(File.Contents("C:\temp\powerbi\pivot.csv"),[Delimiter=",", Encoding=1252]),
#"Promoted Headers1" = Table.PromoteHeaders(Source),
#"Removed Columns1" = Table.RemoveColumns(#"Promoted Headers",{"April_$", "May_$", "June_$"From:
http://social.technet.microsoft.com/wiki/contents/articles/32915.power-bi-merge-query-with-m.aspx
Code mode is not cooperating:
let
Source1 = Csv.Document(File.Contents("C:\temp\powerbi\pivot.csv"),[Delimiter=",", Encoding=1252]),
Source = Csv.Document(File.Contents("C:\temp\powerbi\pivot.csv"),[Delimiter=",", Encoding=1252]),
#"Promoted Headers1" = Table.PromoteHeaders(Source),
#"Removed Columns1" = Table.RemoveColumns(#"Promoted Headers",{"April_$", "May_$", "June_$"}),
#"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Removed Columns", {"Product", "Customer"}, "Attribute", "Value"),
#"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Columns1",{{"Customer", type text}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "Merge", each [Product] & [Customer] & List.First(Text.Split([Attribute],"_"))),
#"Promoted Headers" = Table.PromoteHeaders(Source),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Product", type text}, {"Customer", Int64.Type}, {"April_Qty", Int64.Type}, {"May_Qty", Int64.Type}, {"June_Qty", Int64.Type}, {"April_$", Int64.Type}, {"May_$", Int64.Type}, {"June_$", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"April_Qty", "May_Qty", "June_Qty"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Product", "Customer"}, "Attribute", "Value"),
#"Changed Type2" = Table.TransformColumnTypes(#"Unpivoted Columns",{{"Customer", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type2", "Merge", each [Product] & [Customer] & List.First(Text.Split([Attribute],"_"))),
#"Merged Queries" = Table.NestedJoin(#"Added Custom",{"Merge"},#"Added Custom1",{"Merge"},"NewColumn",JoinKind.LeftOuter),
#"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Value"}, {"NewColumn.Value"}),
#"Removed Columns2" = Table.RemoveColumns(#"Expanded NewColumn",{"Merge"})
in
#"Removed Columns2"