Unpivot Multiple Sections of Data

Community Champion

10 years ago

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"

djnww
Impactful Individual
10 years ago
Anonymous,

Actually, it would definitely be useful if Power BI offered a method to do what you have just mentioned. Most companies have their Excel data with months along the top row.

Rather than have Excel users having to redesign all their spreadsheets to be more data friendly (which ends up being less data entry friendly), it would be good if Power Bi Desktop had some functionality to transpose these tables into a data friendly Power Bi format. In other words, a user might be able to tell Power Bi that the headers from Cell A3 onwards are actually categories/dimensions rather than measures.

- Daniel
- Anonymous
  Not applicable
  10 years ago
  I totally agree! With self service BI the target user is more of a general business user than someone who invests time in learning DAX or M.
  
  With that said I'm trying to learn everything that I can because I see Power BI being the next "big thing" in business. Learn everything we can now and it'll pay dividends in the end!
Greg_Deckler
Community Champion
10 years ago
Sorry, I agree it is a bit crazy, here's a line by line. Sorry stuff is getting cut off, I can't help that, something wonky is going on pasting this stuff in.

let
// Define the same source twice, "Source" and "Source1". Actually we don't really need to do this, we can define
// the source and essentially split it off into two different processing paths, which is what is actually done here
    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]),
// All of these start with "Source" and modify it
    #"Promoted Headers1" = Table.PromoteHeaders(Source),
// Get rid of offending columns causing us unpivot problems
    #"Removed Columns1" = Table.RemoveColumns(#"Promoted Headers",{"April_$", "May_$", "June_$"
// Unpivot around our Product and Customer columns
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Removed Columns", {"Product", "Customer"
// Make sure the Customer column is Text
    #"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Columns1",{{"Customer", type text
// Add a custom column to merge on, a combination of Product concatenated with Customer concatenated with the first part
// of Attribute split on "_", this gives us something like "X1111April", a unique key we can use to merge
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Merge", each [Product] & [Customer] & List.First(Text.Split([Attribute],"_"))),
// Now we go back and start a separate processing path of Source to account for the other unpivot we want to do
     #"Promoted Headers" = Table.PromoteHeaders(Source),
// Make sure Product is text, this one sort of just snuck in there
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Product", type text
// Get rid of the alternate offending columns causing us unpivot problems
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"April_Qty", "May_Qty", "June_Qty"
// Unpivot around Product and Customer
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Product", "Customer"
// Again, make sure Customer is text
    #"Changed Type2" = Table.TransformColumnTypes(#"Unpivoted Columns",{{"Customer", type text
// Create the same merge column as before so that we have a unique key to merge on
    #"Added Custom" = Table.AddColumn(#"Changed Type2", "Merge", each [Product] & [Customer] & List.First(Text.Split([Attribute],"_"))),
// Merge the queries, this actually merges #Added Custom and #Added Custom1 on the merge columns in each query path
    #"Merged Queries" = Table.NestedJoin(#"Added Custom",{"Merge"
// Expand the table that results from the merge for the "Value" column in the merged query
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Value"
// We no longer need the merge column so get rid of it
    #"Removed Columns2" = Table.RemoveColumns(#"Expanded NewColumn",{"Merge"
in
    #"Removed Columns2"
- ImkeF
  Community Champion
  10 years ago
  If you prepare your data accordingly, you could actually use a pretty short code like this:
  
  let Source = PreparedTable, // Unpivot Fact Cols to create unique Headers Unpivot = Table.UnpivotOtherColumns(Source, {"Customer", "Product", "QtyOrAmount"}, "Attribute", "Value"), NewHeader = Table.CombineColumns(Unpivot,{"Customer", "Product", "QtyOrAmount"},Combiner.CombineTextByDelimiter(".", QuoteStyle.None),"Merged"), // Define new Header fields Sort = Table.Sort(NewHeader,{{"Merged", Order.Ascending}}), // Voila :-) Pivot = Table.Pivot(Sort, List.Distinct(Sort[Merged]), "Merged", "Value", List.Sum) in Pivot
  
  see: http://www.thebiccountant.com/2015/08/12/how-to-pivot-multiple-measurescolumns-in-power-query/
  
  The preparation step would be to add a column to each of your csv tables, containing "Amount" or "Qty" and then append(combine) these tables. So this is how the full code would look like:
  
  let Source1 = Table.AddColumn(Csv.Document(File.Contents("C:\temp\powerbi\Amount.csv"),[Delimiter=",", Encoding=1252]), "QtyOrAmount2, each "Amount"), Source2 = Table.AddColumn(Csv.Document(File.Contents("C:\temp\powerbi\Quantity.csv"),[Delimiter=",", Encoding=1252]), "QtyOrAmount2, each "Qty"), PreparedTable = Table.Combine({Source,Source2}), Unpivot = Table.UnpivotOtherColumns(Source, {"Customer", "Product", "QtyOrAmount"}, "Attribute", "Value"), NewHeader = Table.CombineColumns(Unpivot,{"Customer", "Product", "QtyOrAmount"},Combiner.CombineTextByDelimiter(".", QuoteStyle.None),"Merged"), Sort = Table.Sort(NewHeader,{{"Merged", Order.Ascending}}), Pivot = Table.Pivot(Sort, List.Distinct(Sort[Merged]), "Merged", "Value", List.Sum) in Pivot
  
  Greg_Deckler your problem with the code being cut is due to your browser. Switch to Firefox or Opera and it will be fine again.
  - Anonymous
    Not applicable
    10 years ago
    Greg_Deckler ImkeF You guys are awesome! As soon as I'm back in the office I'm giving this a go! This is M correct? Where'd you guys learn all of this? I'm still trying to learn all of the DAX commands. Any tips or resources would be awesome.

Forum Discussion

Related content

Pivot / Unpivot?

Unpivot Column

Advanced unpivoting multiple columns

Unpivot Question

Unpivot