Help with Unpivot/PowerQuery

[megera716]

I'm pretty savvy to Unpivot, Split Column and Pivot Columns within Power Query but I'm really struggling with this one. Hoping I'm missing something obvious or just doing my steps in the wrong order

I have a table like so with data from 2013-2019 (3 columns for every year with Member/Sales/Rep):

Customer Name	Customer Type	2013 Member	2013 Sales	2013 Rep	2014 Member	2014 Sales	2014 Rep	2015...
Apple	ABC	Y	15000	SJ	Y	25000	SJ
Google	XYZ	N	0		Y	10000	CD

I am trying to get it into a table format that will look like this (thinking I would split the Member/Sales/Rep columns with a year in it on 4 characters to isolate the year) so that I can pivot by rep or year or whatever I fancy:

Customer Name	Customer Type	Year	Member	Sales	Rep
Apple	ABC	2013	Y	15000	SJ
Apple	ABC	2014	Y	25000	SJ
Google	XYZ	2013	N	0
Google	XYZ	2014	Y	10000	CD

I have tried seemingly every iteration of selecting or not selecting columns, unpivoting selected or unpivoting other. I think my last try, I selected just the sales and rep columns for each year, Unpivot Selected and ended up with this:

Customer Name	Customer Type	Attribute	Value
Apple	ABC	2013 Sales	15000
Apple	ABC	2013 Rep	SJ
Apple	ABC	2014 Sales	25000
Apple	ABC	2014 Rep	SJ
Google	XYZ	2013 Sales	0
Google	XYZ	2013 Rep
Google	XYZ	2014 Sales	10000
Google	XYZ	2014 Rep	CD

Not what I'm going for . Can someone please advise as to what columns I should select/not select and what steps? Thank you in advance!

 

[alansidman]

Here is the Mcode I used to achieve your results.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer Name", type text}, {"Customer Type", type text}, {"2013 Member", type text}, {"2013 Sales", Int64.Type}, {"2013 Rep", type text}, {"2014 Member", type text}, {"2014 Sales", Int64.Type}, {"2014 Rep", type text}, {"2015...", type any}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Customer Name", "Customer Type"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Member", each if Text.Contains([Attribute],"Member") then [Value] else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Sales", each if Text.Contains([Attribute],"Sales") then [Value] else null),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Rep", each if Text.Contains([Attribute], "Rep") then[Value] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom2",{"Rep", "Sales"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Member] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Value"}),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Removed Columns", "Attribute", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Attribute.1", "Attribute.2"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Split Column by Character Transition",{"Attribute.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Attribute.1", "Year"}})
in
    #"Renamed Columns"

Book1
	A	B	C	D	E	F
2	Apple	ABC	2013	Y	15000	SJ
3	Apple	ABC	2014	Y	25000	SJ
4	Google	XYZ	2013	N	0	CD
5	Google	XYZ	2014	Y	10000	CD
Sheet2

 

[megera716]

Alan, thanks for writing that code for me! I'm definitely not at all familiar with VBA. Do I just open the editor and paste that in? For the "2015..." column, I put that to indicate that the Member/Sales/Rep columns continue for 2015-2019, but that's not the actual text of the column header. Can I just copy and paste what you wrote for 2013 and 2014 to populate for the remaining years?

 

[CA_Punit]

This is not a VBA code. You have to go to Advance Editor in Power Query and Just paste the code in advance editor.

 

[alansidman]

MCode
select your range/table
Data - From Table
it will open Power Query Editor
find and open Advanced Editor
replace whole code there with code from the post
be sure the name of the source table is the same as in the code (here: Table1)
Done
Close&Load

 

[sandy666]

here is compatible M with older version of PQ

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    UOC = Table.UnpivotOtherColumns(Source, {"Customer Name", "Customer Type"}, "Attribute", "Value"),
    Members = Table.AddColumn(UOC, "Member", each if Text.Contains([Attribute],"Member") then [Value] else null),
    Sales = Table.AddColumn(Members, "Sales", each if Text.Contains([Attribute],"Sales") then [Value] else null),
    Rep = Table.AddColumn(Sales, "Rep", each if Text.Contains([Attribute], "Rep") then[Value] else null),
    FillU = Table.FillUp(Rep,{"Rep", "Sales"}),
    FilterNull = Table.SelectRows(FillU, each ([Member] <> null)),
    RC = Table.RemoveColumns(FilterNull,{"Value"}),
    SCBD = Table.SplitColumn(RC, "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    RC1 = Table.RemoveColumns(SCBD,{"Attribute.2"}),
    Ren = Table.RenameColumns(RC1,{{"Attribute.1", "Year"}})
in
    Ren

 

[megera716]

here is compatible M with older version of PQ

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    UOC = Table.UnpivotOtherColumns(Source, {"Customer Name", "Customer Type"}, "Attribute", "Value"),
    Members = Table.AddColumn(UOC, "Member", each if Text.Contains([Attribute],"Member") then [Value] else null),
    Sales = Table.AddColumn(Members, "Sales", each if Text.Contains([Attribute],"Sales") then [Value] else null),
    Rep = Table.AddColumn(Sales, "Rep", each if Text.Contains([Attribute], "Rep") then[Value] else null),
    FillU = Table.FillUp(Rep,{"Rep", "Sales"}),
    FilterNull = Table.SelectRows(FillU, each ([Member] <> null)),
    RC = Table.RemoveColumns(FilterNull,{"Value"}),
    SCBD = Table.SplitColumn(RC, "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    RC1 = Table.RemoveColumns(SCBD,{"Attribute.2"}),
    Ren = Table.RenameColumns(RC1,{{"Attribute.1", "Year"}})
in
    Ren

Sandy! This was incredible! It gave me the exact layout I wanted but it didn't quiiite work -- the rep is offset from the sales by one row. I pasted the exact code you provided other than I changed the name to Table3 (which is the actual name). Can you help here?

Name	Year	Member	Sales	Rep
Apple	2013	N	0​
Apple	2014	N	0​
Apple	2015	N	0​
Apple	2016	N	0​
Apple	2017	N	6000​
Apple	2018	N	14000​	HK
Apple	2019	N	15980​	HK
Google	2013	N	0​	BB
Google	2014	N	0​
Google	2015	N	14940​
Google	2016	N	0​	BB
Google	2017	N	0​
Google	2018	N	0​
Google	2019	N	0​

 

[sandy666]

ask @alansidman
i changed one line only

btw. post source example for the result in post#7 (and expected result)

 

[megera716]

Ooh, I didn't know you could tag people!

Alan, I used Sandy's code because I wasn't sure how to add the actual column names (regarding my earlier post where I mentioned that "2015..." was not an actual column header) and his didn't specify column names. Can you advise on why the rep is off by one row from the rest of the data for that year?

 

[sandy666]

this is Alan's M-code