Results 1 to 4 of 4

Thread: Power query - Merge queries - Lost Columns

  1. #1
    Neophyte AJRae's Avatar
    Join Date
    Jul 2021
    Posts
    3
    Articles
    0
    Excel Version
    365 for Business

    Question Power query - Merge queries - Lost Columns



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,701
    Articles
    0
    Excel Version
    Office 365 Subscription
    You need to override the hard-coded column expansion.

    Have a look here: https://community.powerbi.com/t5/Des...es/td-p/471874
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #3
    Neophyte AJRae's Avatar
    Join Date
    Jul 2021
    Posts
    3
    Articles
    0
    Excel Version
    365 for Business
    @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!

  4. #4
    Neophyte AJRae's Avatar
    Join Date
    Jul 2021
    Posts
    3
    Articles
    0
    Excel Version
    365 for Business

    Smile

    Quote Originally Posted by AJRae View Post
    @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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •