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