Unpivot Multiple Sections of Data

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.

Greg_Deckler

Community Champion

10 years ago

Yes, this is M code. I started learning M by using the query editor and then switching to View | Advanced and looking at the code. Then you can use references like:

M: https://msdn.microsoft.com/library/Mt253322?ui=en-US&rs=en-US&ad=US

DAX: https://msdn.microsoft.com/en-us/library/ee634396.aspx?f=255&MSPPError=-2147217396

I'm in Chrome on this session so let's see if the code block works now for pasting:

let
// comment
    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

Not applicable

10 years ago

ImkeF I've gone through this and I think I understand it. The only issue that I'm having is this will be a live connection through Access, so the parts about the CSV file wouldn't apply to my situation (at least if I understand this...). I know you can probably apply the same logic to the query through Access, but this is my first foray with M so I'm pretty lost. Could you help me out with preparing the "Prepared Table" using an Access file?

Anonymous

Not applicable

10 years ago

I have a similar issue. I've got a set of data that's exported from a database to an Excel file. I can't modify that source Excel file so all of my transformation needs to be done in Power BI Desktop.

In the source data, each row is one parent, but there can be one or more children per parent/row. The children's information is pivoted into multiple sets columns on the right of the table. Here's an example set of data I made up:

Source data

Initial,,,,,,,,,,,,,,,
ID,Parent_Fname,Parent_Lname,Parent_Address,Child01_Fname,Child01_Lname,Child01_Grade,Child01_Status,Child02_Fname,Child02_Lname,Child02_Grade,Child02_Status,Child03_Fname,Child03_Lname,Child03_Grade,Child03_Status
920150,Komyhar,Basmaci,23 Fox Run,Laurie,Parker Feld,8,D,Kristina,Raffoni,10,B,Robert,Boston,10,B
920200,Thomas,Bass,3 Island Pond Road,David,Parker Feld,9,D,Khalid,Wool,6,B,Timothy,Boston,11,E
920250,John,Bateman,6 Trimble Trail,Rajeev,Cosentino,7,D,Thomas,Wagner,9,D,William,Jimenez,6,B
220175,Garrett,Baublitz,11 Chisholm Farm Drive,Beth,Heiderscheidt,12,D,Benjamin,Gomes,9,E,,,11,B
220200,Michael,Bauer,6 Sewall Rd.,John,Sweet,9,E,Kevin,Roberts,11,D,Arul,Edminster,10,E
820550,Eric,Bauer,6 Carwin Drive,Joseph,Morris,8,D,,,9,D,,,9,E
220125,Emmett,Baumgart,PO Box 257,Matthew,Palmer,9,D,,,6,D,,,11,D
820400,Bryce,Bayer,13 Peabody Drive,Eugene,Broadrick,11,C,,,7,E,,,9,E
220150,Ajel,Beach,75 Bayside Road,M.,Twombly,8,D,,,9,C,Eric,Caradonna,6,A
720250,Matthew,Beahn,39 Haigh Road,Stephen,Bouvier,6,A,Bradley,Kutner,11,B,Keri,Davis,8,C
920125,Benedict,Beck,190 Kings Highway,Eileen,O'Friel,11,D,Brian,Knudsen,9,A,Brooks,Davis,12,C
920350,Francis,Berger,PO Box 734,Timothy,McLaughlin,12,B,Arthur,Strauss,8,D,,,6,A
820600,Ryan,Berger,171 Jones Avenue,Robert,Kennedy,8,B,Jonathan,Douglas,7,C,,,9,D
820500,Benjamin,Berger,127 Drinkwater Road,Thomas,Niznik,8,A,Drew,Forest,12,B,Gregory,Bodine-Pritchard,9,A
920300,Jared,Biggers,12 Alderwood Drive,Barry,Richard,12,B,Clayton,Bond,6,D,Andrew,Hurtado,8,B
,,,,,,,,,,,,,,,

What I need to do is convert this from one line per parents to one line per child. This seems like a perfect use case for the unpivot transformation tool, but I can't get it to work for multiple sections of columns like this.

Here's an example of what the data should look like after the transformation, based on the same example data:

After desired transformation

Final,,,,,,,,,,,,,,,
ID,Parent_Fname,Parent_Lname,Parent_Address,Child_Fname,Child_Lname,Child_Grade,Child_Status,,,,,,,,
920150,Komyhar,Basmaci,23 Fox Run,Laurie,Parker Feld,7,A,,,,,,,,
920151,Komyhar,Basmaci,24 Fox Run,Kristina,Raffoni,10,E,,,,,,,,
920152,Komyhar,Basmaci,25 Fox Run,Robert,Boston,9,A,,,,,,,,
920200,Thomas,Bass,3 Island Pond Road,David,Parker Feld,8,A,,,,,,,,
920201,Thomas,Bass,4 Island Pond Road,Khalid,Wool,12,A,,,,,,,,
920202,Thomas,Bass,5 Island Pond Road,Timothy,Boston,6,C,,,,,,,,
920250,John,Bateman,6 Trimble Trail,Rajeev,Cosentino,9,D,,,,,,,,
920251,John,Bateman,7 Trimble Trail,Thomas,Wagner,11,C,,,,,,,,
920252,John,Bateman,8 Trimble Trail,William,Jimenez,8,B,,,,,,,,
220175,Garrett,Baublitz,11 Chisholm Farm Drive,Beth,Heiderscheidt,11,E,,,,,,,,
220176,Garrett,Baublitz,12 Chisholm Farm Drive,Benjamin,Gomes,7,C,,,,,,,,
220200,Michael,Bauer,6 Sewall Rd.,John,Sweet,12,E,,,,,,,,
220201,Michael,Bauer,7 Sewall Rd.,Kevin,Roberts,11,C,,,,,,,,
220202,Michael,Bauer,8 Sewall Rd.,Arul,Edminster,8,D,,,,,,,,
820550,Eric,Bauer,6 Carwin Drive,Joseph,Morris,9,B,,,,,,,,
220125,Emmett,Baumgart,PO Box 257,Matthew,Palmer,11,E,,,,,,,,
820400,Bryce,Bayer,13 Peabody Drive,Eugene,Broadrick,7,C,,,,,,,,
220150,Ajel,Beach,75 Bayside Road,M.,Twombly,8,A,,,,,,,,
220151,Ajel,Beach,76 Bayside Road,Eric,Caradonna,11,A,,,,,,,,
720250,Matthew,Beahn,39 Haigh Road,Stephen,Bouvier,9,B,,,,,,,,
720251,Matthew,Beahn,40 Haigh Road,Bradley,Kutner,6,A,,,,,,,,
720252,Matthew,Beahn,41 Haigh Road,Keri,Davis,12,E,,,,,,,,
920125,Benedict,Beck,190 Kings Highway,Eileen,O'Friel,12,B,,,,,,,,
920126,Benedict,Beck,191 Kings Highway,Brian,Knudsen,10,D,,,,,,,,
920127,Benedict,Beck,192 Kings Highway,Brooks,Davis,9,A,,,,,,,,
920350,Francis,Berger,PO Box 734,Timothy,McLaughlin,9,B,,,,,,,,
920351,Francis,Berger,PO Box 735,Arthur,Strauss,7,B,,,,,,,,
820600,Ryan,Berger,171 Jones Avenue,Robert,Kennedy,12,A,,,,,,,,
820601,Ryan,Berger,172 Jones Avenue,Jonathan,Douglas,8,E,,,,,,,,
820500,Benjamin,Berger,127 Drinkwater Road,Thomas,Niznik,7,A,,,,,,,,
820501,Benjamin,Berger,128 Drinkwater Road,Drew,Forest,6,D,,,,,,,,
920300,Jared,Biggers,12 Alderwood Drive,Barry,Richard,9,A,,,,,,,,
,,,,,,,,,,,,,,,

One complicaiton to this is that instead of 3 potential children/students per parent, there are up to 7. Because it would be such a bear, I'm scared to know what a custom M/DAX solution would look like. I'm really hoping there's some way to use the unpivot tool that I'm not aware of.

Any help would be greatly appreciated. Thanks in advance!

Forum Discussion

Related content

Pivot / Unpivot?

Unpivot Column

Advanced unpivoting multiple columns

Unpivot Question

Unpivot