I remember seeing either a YouTube video or it was in the Power Query course an example where you wanted to merge data but because there were unique rows in each table, you used append first and then did something to get the same result. I can't find the video and can't remember how to do it.
I am trying to compare period over period inventory. There are a lot of attributes (Bus unit, division, product, entity, org, g/l account, warehouse, qty, mfg price, intercompany markup, std cost, and then the extnded mfg value (qty * mfg cost), extended markup value, and extended std value. There is a separate file for each month. I want to see the month over month change (as well as % change) for qty and the three extended values.
If I do a merge, I get a lot of lines from the second table that are not in the first table and vice versa. Because I have so many attributes, it's a lot of coding to combine. If I append the queries instead, I don't remember the steps required to enable me to calculate the difference between the prior and current amounts.
If anyone remembers where this is covered, can you let me know?
Hi Kelly,
Assuming the files are all the same structure i.e. same column headers, then you should be appending the files. You can do this automatically by putting all the files in a folder and then using the From Folder connector (lesson 2.05). This will automatically append them. If they're not already in a tabular layout, you can still use From Folder, but make sure you do the transformations in the Sample File query so they are applied BEFORE appending the files.
I would do the month on month calculation in Power Pivot using the time intelligence functions (covered in session 10 of the Power Pivot course).
I hope that points you in the right direction.
Mynda
Thanks. The prior vs current month will be fairly random. I could be current month to any random previous month depending on what they ask for. From what I see in the time intelligence functions, my choices are specific to prior month, prior qtr, prior year, etc. Am I missing something maybe?
And just out of curiosity, do you recall where I saw the video where you the goal was to merge but you used append instead and then did some kind of pivoting? I'd like to go over that again for future use.
Hi Kelly,
If the number of periods will be variable, then the DATEADD function may be what you need.
I don't recall ever appending instead of merging. You may be thinking of the video where I used the Sample Query instead of the Final Query to make the transformations before the files are appended. In which case it's this video, but I also cover that in the Power Query course in 2.05 Files From Folder.
Mynda
