Hi All, I've got a situation that I cannot figure out that I'm hoping someone else has run into. I've got a table of sales information with months across the columns. Usually this would be a quick Unpivot when I bring in the data, however I've got both Sales Amount and Sales Quantity which have their own group columns. Here's a very oversimplified example, but it gets the point across: I'm bringing in the data from Acess. Is there any quick way to Unpivot multiple sections of a table? The only other solution I can think of is doing two queries and then joining them back together. Thanks in advance!

This can all be done using the UI only: & this is the code:let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLzA5E+YNLYCEiAGKYWpiCWAW4iVidayQis3QisHUQSxrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, FirstName = _t, LastName = _t, #" Phone1 Type" = _t, #" Phone1 Number" = _t, #" Phone1 Date added" = _t, #" Phone2 Type" = _t, #" Phone2 Number" = _t, #" Phone2 Date added" = _t, #" Phone3 Type" = _t, #" Phone3 Number" = _t, #" Phone3 Date added" = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ID", "FirstName", "LastName"}, "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns","Attribute",Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv),{"Attribute.1", "Attribute.2", "Attribute.3", "Attribute.4"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ID", Int64.Type}, {"FirstName", type text}, {"LastName", type text}, {"Attribute.1", type text}, {"Attribute.2", type text}, {"Attribute.3", type text}, {"Attribute.4", type text}}), #"Split Column by Position" = Table.SplitColumn(#"Changed Type","Attribute.2",Splitter.SplitTextByRepeatedLengths(5),{"Attribute.2.1", "Attribute.2.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Attribute.2.1", type text}, {"Attribute.2.2", Int64.Type}}), #"Merged Columns" = Table.CombineColumns(#"Changed Type1",{"Attribute.2.1", "Attribute.3", "Attribute.4"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"), #"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Attribute.1"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Merged]), "Merged", "Value") in #"Pivoted Column"

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"

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

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"

EDIT: Sorry I answered to quick without looking at your desired result more carefully! So my "solution" does not apply! :smileysad:

Unpivot Multiple Sections of Data | Microsoft Fabric Community

22 Replies

Greg_Deckler
Community Champion
10 years ago
So, what are you wanting the end result to look like?
- Anonymous
  Not applicable
  10 years ago
  Wow, I can't believe that I didn't put that in the original post. It's been a long day... :catindifferent:
  
  Using the example above, I'd ideally like it to look like this...
  
  But if there has to be/it's much easier to have a "New_Column_$" as well, that's perfectly fine too.
  - Greg_Deckler
    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"

Forum Discussion

Unpivot Multiple Sections of Data

22 Replies

Related content

Pivot / Unpivot?

Advanced unpivoting multiple columns

Unpivot Column

Unpivot Question

Unpivot