Unpivot Multiple Sections of Data

[Anonymous]

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!

[Greg_Deckler]

So, what are you wanting the end result to look like?

[Anonymous]

Wow, I can't believe that I didn't put that in the original post. It's been a long day... 

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]

@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]

Wow that's a doozy. Thanks for the help! I'll give it a try.

[Greg_Deckler]

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]

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]

@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?

[ImkeF]

HI @shushicat_san, sorry - completely missed your post.

My instructions on preparations don't make sense to me now, as your data looked pretty fine - so have you been able to solve this meanwhile?

[Anonymous]

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!

[Anonymous]

I've come across this same problem again.

This time, I have rows of people and multiple phone numbers for each person spread across columns:

ID1, First Name1, Last Name1, Phone1 Type, Phone1 Number, Phone1 Date added, Phone2 Type, Phone2 Number, Phone2 Date added, Phone3 Type, Phone3 Number, Phone3 Date added

I need to transform it to one row per phone number, not one row per person, and maintain each person's bio fields on each row:

ID1, First Name1, Last Name1, Phone1 Type, Phone1 Number, Phone1 Date added
ID1, First Name1, Last Name1, Phone2 Type, Phone2 Number, Phone2 Date added
ID1, First Name1, Last Name1, Phone3 Type, Phone3 Number, Phone3 Date added

Anybody know of a way to do this using the Excel or Power BI graphic UI?

I have this issue often and am not the best at using M (certainly not writing it), but if there's some sort of re-usable M "template" I could use, that would work as well.

[ImkeF]

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]

Excellent! Thank you so much, @ImkeF. The video is great. I'm working on this now. We'll see how it goes.

[ImkeF]

Great - pls let me know if you need any more help here!

[Anonymous]

Did you get an email from me? It might have gone to spam.

[Sean]

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

[Anonymous]

Sean, was this supposed to be a blank post, or is there perhaps an issue preventing me from seeing your post content?

[ImkeF]

This is one way:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bVVdc9o6EP0rGp7bDpiCyyPmI2m4tAzJTB5687DBW1tBlu5IMpT8+rta2cZMOmNbEobds+ecXX79GsyS4WgyHHwabEx1KcHSLgNXwUHSLhmLtfkj9rWmwz9QW4m02YE9ohVrVDmdvtG9DL+30nmpgbZ7+P3b6BBgFCJn4SPzitaHg3He6Ourl0+MIRmG41NpKnARQljG4rtToHOxM/TYGwgJl3CS+QcYsxZGCYpfPxujaJk2AJ5kZXx5uUEwoseqQ8AsPJhSc36PFYTdVDxZWb0qpBWk4ureEE+0WRiHmko2tE+b9F0Fz1BotD1gz1IpCVXIISvU+N6BCwASkiGd0PEOrEXPTEH9qqR/Z6RiUUpXGlWJNdhKLK08BSky9CUt9yhztO5Q0hp+OkqapBnqN6hkKOTOVOgaPCu648UcdBCiClt5KAFVhMA1TMUjnkEpsc+/XEl6PCP6XsQNnjhT1Nq10QOOua1DvFVOWJznmKx/ZP9bMpww+ysrDzdpF2DPUnflPhDj/4WCt8aS3Xrui9fsw2nVsZsEdldV1ZFbFcCO3P0UGZk8mQQNt+B9iWe2l6puBIzX9ObUFNgU8ZXpy+zlwNrAJRY6FjuEV5NfujpWdUEO4O+Sp6noYxtr0YVOb3S6rYXJmr9FjRAOgZJ0IiijIyO0jbINWj2dDbn38leqFlfKiWjIjeb2DSXOOVfatsWVFkrH4o9n4h5kUbbJHj0pg9w7pj7JqB8HClVCrjBg2NQ+dkXju2AaGwZFaOpWz0XbklEz8jDm8sCqYWRqNhQbqQsn7gnBGULklVTI6X/+Ww+HSbqmWaX6Fsys5Ibe6Dp3/M1ZB8+Yo+uB4PbpUIyZgbUFfeC3GdqCa2iMk46/DvrzZXugSVmUinuBQ2XcAb6sLRNloXYfvXtlnZw0ZSftL4y4SzhKR+LBaHRifkJd46A3VzeoiaZW54ybRYMvOcLS1IXisZTeeGzWd+8kuvc6Ma55kzRYVx/PNBZtq3g36n7Idy2PTeZA6NKyVdbGouvGUYB0Z7EwNg7hXGr8vLPS07Cx7QSfd6QzmAewGF5lsiAkURoxVzTrzsbk1ylIMzME3cs2VpdxoeASp31GfyG9/p3rPKK8r60n63fEvbz8Dw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Parent_Fname = _t, Parent_Lname = _t, Parent_Address = _t, Child01_Fname = _t, Child01_Lname = _t, Child01_Grade = _t, Child01_Status = _t, Child02_Fname = _t, Child02_Lname = _t, Child02_Grade = _t, Child02_Status = _t, Child03_Fname = _t, Child03_Lname = _t, Child03_Grade = _t, Child03_Status = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ID", "Parent_Fname", "Parent_Lname", "Parent_Address"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "")),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows","Attribute",Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, false),{"Attribute.1", "Attribute.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Attribute.2]), "Attribute.2", "Value")
in
    #"Pivoted Column"

If you paste this code into the advanced editor each step should be easy to follow, as it already contains some data.

[Anonymous]

@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]

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"