Jul 22 2020 07:25 AM
Dear all,
For one of my market analysis projects I'm looking for someone with a bit more experience with Power Pivot and Power Query in Excel.
I have been able to successfully combine separate files through power query on the one hand and I have been able to unpivot part of a table through power query as well. However for a quarterly market update I'm now looking to combine both steps.
So every quarter I receive an excel file with market data on the past 3 months. I have uploaded an (dutch) example for Q1 in 2018 where I have removed the product names. As you can see the first column contains the product names and the next three each refer to a month in the quarter.
Once unpivoted I have a nice and raw database. However since we get quarterly updates, I'll have to update the file each time. I figured it might be possible to tell powerquery to always unpivot each file on columns 2, 3 & 4 and then combine them. But I'm not sure how (nor if) that's possible.
So perhaps someone here can help?
Thanks in advance, much appreciated! :)
Ps. my file and excel is in Dutch, but power query is in English so directions in either language is OK for me! :)
Jul 22 2020 10:56 PM
Hi @BjornK91
Pls share the screenshot of your step, are you combining the query via folder ? If possible attached sample scenario with few records with your desired results manually place.
Regards, Faraz Shaikh
Jul 23 2020 12:35 AM
@Faraz Shaikh
First of all: thanks for your prompt response, really appreciate it that you're trying to help! :)
As for your follow-up questions:
1. Yest I'm combining the files via the folder.
2. I've added two different sample files, which I'd like to combine into the query result sample.
So basically the powerquery should combine the unpivoted files and use the dates from the column names as date values in the rows, along with the SKU and the value for that period.
Jul 23 2020 02:48 AM
Hi @BjornK91,
What feel might be unpivot step is incorrect, should be unpivot other columns
if still you are facing the issue can you please attach your query file, I need to see the step where the issue is happening.
Regards, Faraz Shaikh