PowerQuery: Combine files & Unpivot

%3CLINGO-SUB%20id%3D%22lingo-sub-1538446%22%20slang%3D%22en-US%22%3EPowerQuery%3A%20Combine%20files%20%26amp%3B%20Unpivot%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1538446%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20all%2C%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20one%20of%20my%20market%20analysis%20projects%20I'm%20looking%20for%20someone%20with%20a%20bit%20more%20experience%20with%20Power%20Pivot%20and%20Power%20Query%20in%20Excel.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20been%20able%20to%20successfully%20combine%20separate%20files%20through%20power%20query%20on%20the%20one%20hand%20and%20I%20have%20been%20able%20to%20unpivot%20part%20of%20a%20table%20through%20power%20query%20as%20well.%20However%20for%20a%20quarterly%20market%20update%20I'm%20now%20looking%20to%20combine%20both%20steps.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3ESo%20every%20quarter%20I%20receive%20an%20excel%20file%20with%20market%20data%20on%20the%20past%203%20months.%20I%20have%20uploaded%20an%20(dutch)%20example%20for%20Q1%20in%202018%20where%20I%20have%20removed%20the%20product%20names.%20As%20you%20can%20see%20the%20first%20column%20contains%20the%20product%20names%20and%20the%20next%20three%20each%20refer%20to%20a%20month%20in%20the%20quarter.%3CBR%20%2F%3E%3CBR%20%2F%3EOnce%20unpivoted%20I%20have%20a%20nice%20and%20raw%20database.%20However%20since%20we%20get%20quarterly%20updates%2C%20I'll%20have%20to%20update%20the%20file%20each%20time.%20I%20figured%20it%20might%20be%20possible%20to%20tell%20powerquery%20to%20always%20unpivot%20each%20file%20on%20columns%202%2C%203%20%26amp%3B%204%20and%20then%20combine%20them.%20But%20I'm%20not%20sure%20how%20(nor%20if)%20that's%20possible.%3CBR%20%2F%3E%3CBR%20%2F%3ESo%20perhaps%20someone%20here%20can%20help%3F%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20in%20advance%2C%20much%20appreciated!%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3CBR%20%2F%3E%3CBR%20%2F%3EPs.%20my%20file%20and%20excel%20is%20in%20Dutch%2C%20but%20power%20query%20is%20in%20English%20so%20directions%20in%20either%20language%20is%20OK%20for%20me!%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1538446%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1540277%22%20slang%3D%22en-US%22%3ERe%3A%20PowerQuery%3A%20Combine%20files%20%26amp%3B%20Unpivot%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1540277%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F736441%22%20target%3D%22_blank%22%3E%40BjornK91%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPls%20share%20the%20screenshot%20of%20your%20step%2C%20are%20you%20combining%20the%20query%20via%20folder%20%3F%20If%20possible%20attached%20sample%20scenario%20with%20few%20records%20with%20your%20desired%20results%20manually%20place.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%20Faraz%20Shaikh%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1540428%22%20slang%3D%22en-US%22%3ERe%3A%20PowerQuery%3A%20Combine%20files%20%26amp%3B%20Unpivot%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1540428%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F156456%22%20target%3D%22_blank%22%3E%40Faraz%20Shaikh%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EFirst%20of%20all%3A%20thanks%20for%20your%20prompt%20response%2C%20really%20appreciate%20it%20that%20you're%20trying%20to%20help!%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3CBR%20%2F%3E%3CBR%20%2F%3EAs%20for%20your%20follow-up%20questions%3A%3CBR%20%2F%3E%3CBR%20%2F%3E1.%26nbsp%3B%20Yest%20I'm%20combining%20the%20files%20via%20the%20folder.%3CBR%20%2F%3E2.%20I've%20added%20two%20different%20sample%20files%2C%20which%20I'd%20like%20to%20combine%20into%20the%20query%20result%20sample.%3CBR%20%2F%3E%3CBR%20%2F%3ESo%20basically%20the%20powerquery%20should%20combine%20the%20unpivoted%20files%20and%20use%20the%20dates%20from%20the%20column%20names%20as%20date%20values%20in%20the%20rows%2C%20along%20with%20the%20SKU%20and%20the%20value%20for%20that%20period.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1540653%22%20slang%3D%22en-US%22%3ERe%3A%20PowerQuery%3A%20Combine%20files%20%26amp%3B%20Unpivot%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1540653%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F736441%22%20target%3D%22_blank%22%3E%40BjornK91%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20feel%20might%20be%20unpivot%20step%20is%20incorrect%2C%20should%20be%20unpivot%20other%20columns%26nbsp%3B%3C%2FP%3E%3CP%3Eif%20still%20you%20are%20facing%20the%20issue%20can%20you%20please%20attach%20your%20query%20file%2C%20I%20need%20to%20see%20the%20step%20where%20the%20issue%20is%20happening.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%20Faraz%20Shaikh%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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!

3 Replies

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 

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

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