Transform file that has dates as individual columns

[seerauber]

Hi all,

I'm not sure how to phrase this question clearly, so apologies in advance if this is confusing.

I am receiving a 'flat file' of sorts from a vendor that provides me with data on search engine keyword rankings by different products. I would like to load all of this data into my PowerBi model, however the file comes with a new column for each date, with the search ranking values for each product+keyword combination populated in the daily column cells. I'll post a short example of what I mean below. I suspect this transformation can be made in Power Query, but I've not been able to turn up any guides or articles to get me started.

Currently this file has ~10k rows, I haven't done the math but after the transformation that number will be significantly bigger, into the 1m+ range...

Any help or references that anyone can share would be very much appreciated! Please let me know if I can clarify the situation any further. Thank you!


How the file actually comes:

Product	Marketplace	Keyword	Estimated Search Volume	03/01/2021	03/02/2021	03/03/2021
SKU 1	US	Keyword 1	#​	35​	Over page 5	34​
SKU 2	US	Keyword 1	#​	Over page 5	Over page 5	Over page 5
SKU 3	US	Keyword 1	#​	14​	13​	12​
SKU 1	US	Keyword 2	#​	75​	88​	70​
SKU 2	US	Keyword 2	#​	Over page 5	8​	7​
SKU 3	US	Keyword 2	#​	14​	14​	14​

How I believe the file needs to be structured:

Product	Marketplace	Keyword	Estimated Search Volume	Search Position	Date
SKU 1	US	Keyword 1	#	35​	3/1/2021​
SKU 1	US	Keyword 2	#	75​	3/1/2021​
SKU 2	US	Keyword 1	#	Over page 5	3/1/2021​
SKU 2	US	Keyword 2	#	Over page 5	3/1/2021​
SKU 3	US	Keyword 1	#	14​	3/1/2021​
SKU 3	US	Keyword 2	#	14​	3/1/2021​
SKU 1	US	Keyword 1	#	Over page 5	3/2/2021​
SKU 1	US	Keyword 2	#	75​	3/2/2021​
SKU 2	US	Keyword 1	#	Over page 5	3/2/2021​
SKU 2	US	Keyword 2	#	8​	3/2/2021​
SKU 3	US	Keyword 1	#	13​	3/2/2021​
SKU 3	US	Keyword 2	#	14​	3/2/2021​
SKU 1	US	Keyword 1	#	34​	3/3/2021​
SKU 1	US	Keyword 2	#	70​	3/3/2021​
SKU 2	US	Keyword 1	#	Over page 5	3/3/2021​
SKU 2	US	Keyword 2	#	7​	3/3/2021​
SKU 3	US	Keyword 1	#	12​	3/3/2021​
SKU 3	US	Keyword 2	#	14​	3/3/2021​

 

[lrobbo314]

Like this?

New__Document (82).xlsx
	A	B	C	D	E	F
1	Product	Marketplace	Keyword	Estimated Search Volume	Date	SearchPosition
2	SKU 1	US	Keyword 1	#	3/1/2021	35
3	SKU 1	US	Keyword 2	#	3/1/2021	75
4	SKU 2	US	Keyword 1	#	3/1/2021	Over page 5
5	SKU 2	US	Keyword 2	#	3/1/2021	Over page 5
6	SKU 3	US	Keyword 1	#	3/1/2021	14
7	SKU 3	US	Keyword 2	#	3/1/2021	14
8	SKU 1	US	Keyword 2	#	3/2/2021	88
9	SKU 1	US	Keyword 1	#	3/2/2021	Over page 5
10	SKU 2	US	Keyword 2	#	3/2/2021	8
11	SKU 2	US	Keyword 1	#	3/2/2021	Over page 5
12	SKU 3	US	Keyword 1	#	3/2/2021	13
13	SKU 3	US	Keyword 2	#	3/2/2021	14
14	SKU 1	US	Keyword 1	#	3/3/2021	34
15	SKU 1	US	Keyword 2	#	3/3/2021	70
16	SKU 2	US	Keyword 2	#	3/3/2021	7
17	SKU 2	US	Keyword 1	#	3/3/2021	Over page 5
18	SKU 3	US	Keyword 1	#	3/3/2021	12
19	SKU 3	US	Keyword 2	#	3/3/2021	14
Table3 (2)

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {"Product", "Marketplace", "Keyword", "Estimated Search Volume"}, "Date", "SearchPosition"),
    Sort = Table.Sort(Unpivot,{{"Date", Order.Ascending}, {"Product", Order.Ascending}, {"SearchPosition", Order.Ascending}})
in
    Sort