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]

I'm not understanding your question. Please explain using an example of what is in my example and what should be in the example. Based upon your original post, my results look exactly like your expected results. Please clarify. If your sample is not representative of your actual data, please identify the differences or show us a modified sample.

 

[alansidman]

Book1
	A	B	C	D	E	F
1	Customer Name	Customer Type	Year	Member	Sales	Rep
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
Sheet3

 

[megera716]

@alansidman I know this is a couple months old but I'm hoping you might be willing to help out again! When I run the code you provided, something is off as I mentioned earlier. I thought I could fix it by just deleting one cell from the column to shift the data up but it's still flip-flopped.

I start with this:

Name

2013 Sales

2013 Member

2013 Rep

2014 Sales

2014 Member

2014 Rep

2015 Sales

2015 Member

2015 Rep

2016 Sales

2016 Member

2016 Rep

2017 Sales

2017 Member

2017 Rep

2018 Sales

2018 Member

2018 Rep

2019 Sales

2019 Member

2019 Rep

GooberCo

0​

N

0​

N

0​

N

0​

N

0​

N

0​

N

69,000​

Y

CA

Google

17,400​

Y

AM

17,600​

Y

HP

34,880​

Y

HP

32,000​

Y

HP

24,000​

Y

FG

13,000​

Y

HK

13,000​

N

HK

And after I run the code you wrote, it gives me this. At first glance, it looks like the rep is just off by one row so that's why I tried just shifting it up one.

Type	Name	Year	Member	Sales	Rep
Vendor	GooberCo	2013	N	0​
Vendor	GooberCo	2014	N	0​
Vendor	GooberCo	2015	N	0​
Vendor	GooberCo	2016	N	0​
Vendor	GooberCo	2017	N	0​
Vendor	GooberCo	2018	N	69000​
Vendor	GooberCo	2019	Y	17400​	CA
Vendor	Google	2013	Y	17600​	AM
Vendor	Google	2014	Y	34880​	HP
Vendor	Google	2015	Y	32000​	HP
Vendor	Google	2016	Y	24000​	HP
Vendor	Google	2017	Y	13000​	FG
Vendor	Google	2018	Y	13000​	HK
Vendor	Google	2019	N	11200​	HK

But you can also see that if I shift it up one, it would put rep "AM" on 2019 for GooberCo but really, "AM" is the rep on Google for 2013 (which is correct).. Any ideas how I can fix this? I don't know enough about m-code to even start to figure out where it's going wrong .

 

[alansidman]

This looks like it is correct now

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"2013 Sales", type text}, {"2013 Member", type text}, {"2013 Rep", type text}, {"2014 Sales", type text}, {"2014 Member", type text}, {"2014 Rep", type text}, {"2015 Sales", type text}, {"2015 Member", type text}, {"2015 Rep", type text}, {"2016 Sales", type text}, {"2016 Member", type text}, {"2016 Rep", type text}, {"2017 Sales", type text}, {"2017 Member", type text}, {"2017 Rep", type text}, {"2018 Sales", type text}, {"2018 Member", type text}, {"2018 Rep", type text}, {"2019 Sales", type text}, {"2019 Member", type text}, {"2019 Rep", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", Int64.Type}, {"Attribute.2", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Attribute.1", Order.Ascending}}),
    #"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[Attribute.2]), "Attribute.2", "Value")
in
    #"Pivoted Column"

Book3
	A	B	C	D	E
7	Name	Attribute.1	Sales	Member	Rep
8	GooberCo	2013	0	N
9	GooberCo	2014	0	N
10	GooberCo	2015	0	N
11	GooberCo	2016	0	N
12	GooberCo	2017	0	N
13	GooberCo	2018	0	N
14	GooberCo	2019	69,000	Y	CA
15	Google	2013	17,400	Y	AM
16	Google	2014	17,600	Y	HP
17	Google	2015	34,880	Y	HP
18	Google	2016	32,000	Y	HP
19	Google	2017	24,000	Y	FG
20	Google	2018	13,000	Y	HK
21	Google	2019	13,000	N	HK
Sheet1

 

[megera716]

@alansidman thank you very much! Both for the corrected code and being willing to return to my lowly thread months later . You have saved me DAYS of work (as I'm running the code on a table with 1200 rows of customers!)