Power query - Merge queries - Lost Columns

AJRae

New member
Joined
Jul 20, 2021
Messages
3
Reaction score
0
Points
0
Excel Version(s)
365 for Business
Hi!, I'm completely new to the forum and a new PQ user (advanced excel user, not so much VBA). I have a query merge issue I need help with, pretty please...

I have created a merge of the following 2 subsidiary files:

Query 1 – Mapping Query (connection only): Chart of accounts and budget code mapping document
Query 2 – P&L by Subsidiary (connection only): a cleaned up Profit and Loss (P&L) Report exported from Quickbooks.

= Query 3 – Merged Query (Table in Budget workbook): Merged on the Account Name and maps the P&L GL accounts and data against the Budget Codes in the mapping file.

Then we use SumIfs on a separate tab in our Budget workbook to consolidate the subsidiary data for the Budget Variance Report which is updated monthly.

The problem I’m having is that when I refresh Query 2 to add the next month (let's say April) which works fine, in Query 3, only any new rows are added to the Excel Table in the workbook but the new month’s column with the data isn’t included. So, in Query 3, I hacked the M code by manually adding the column name of the new month (April) to the Expand Table step, like this:

= Table.Expand TableColumn(Source, "CO Query", {"Account Name", "Oct. 2020", "Nov. 2020", "Dec. 2020", "Jan. 2021", "Feb. 2021", "Mar. 2021", "Apr. 2021"}, {"CO Query.Account Name", "CO Query.Oct. 2020", "CO Query.Nov. 2020", "CO Query.Dec. 2020", "CO Query.Jan. 2021", "CO Query.Feb. 2021", "CO Query.Mar. 2021", "CO Query.Apr. 2021"})

This worked for the first additional month (April) but when I tried my hack and then refreshed Query 3 again for the next month (May), it dropped the previously added month (April) in the Excel table (not in the query) but added the new month (May) and it's data in the table correctly. In the Query 3, it now showed April's Type as a "table icon with a ?". So I changed the type back to Currency, and then on refresh it did add the April column to the Excel table in the workbook but zeroed out the April data (only).

I'm hoping that there is some M code fix to make the expand in Query 3 dynamic to add and update the new monthly columns on just the refresh. Or a few quick fixes in the query. But how to do that is beyond my knowledge at this stage.

Any assistance would be super-duper, gratefully appreciated. :):)

Cheers,
AJ
 
@AliGW Thanks a bunch for the referral. I'm new to M so am just working through how to apply the recommendations in those related posts to my specific code...
Thanks again!
 
@AliGW Thanks a bunch for the referral. I'm new to M so am just working through how to apply the recommendations in those related posts to my specific code...
Thanks again!

UPDATE: I found the below YouTube that was very simple (for a neophyte like me) and the solution worked! Thanks again @AliGW for pointing me in the right direction. I didn't know what to Google (turned out to be "dynamic columns") to get the correct videos to pop up. YEAH! HAPPY DANCE!

https://www.youtube.com/watch?v=ipRQONz4GOE

Cheers,
AJ
 
Back
Top