Post reply

importing from PowerQuery... but with multiple powerqueries

  • April 28, 2025 at 11:37 pm

    #4585280

    I finally got the PowerQuery source to "show" in SSIS (so I can see columns and data now).

    In my case, my data is a bit weird. The data reads from a PDF table, which works. In the table are both "Purchase" line items and "Rebate" line items, which refer back to a line item in the Purchase set.  The relationship between Purchase line items and Rebate line items is sort of (0,1). A Purchase may have at most one related rebate, and a rebate cannot exist without a related Purchase.

    In order to make the data easier to work with, I split the full Purchases table into Purchase records and Rebate records, and then left joined Purchases to Rebates and everything works. My question is this: How do I set this up in PowerQuery so that I load all 4 queries and then merge them before writing them to the database? I can write them just fine. They map - everything's fine there. but how do I reference the "intermediate" PowerQueries so I can create the final one? Is that where variables come in? (Doesn't look like it).

    Is there something I can read about doing something like this? Right now I'm doing something like:

    1. create a powerQuery that does most of the general cleanup, returns a proper table, but the Purchases and Rebates (sort of a self-join) are in the same table so...
    2. from #1, create two references and use one for just Purchases and the other for just Rebates. Finish cleanup (two steps are different for the two queries).
    3. Merge the Purchases and Rebates queries back together

    But how do I do that with the PowerQuery editor in SSIS? can the powerqueries reference each other somehow (the same way you do in PowerQuery in Excel or PowerBI?) Is there documentation on that part somewhere?

    thanks!

    Pieter

  • April 29, 2025 at 1:25 am

    #4585309

    Oh I'm an idiot.

    Create separate PowerQueries that read a single table and clean all that junk. (Just duplicate the query instead of referencing it). Then have one return the Purchases table and then create another to return the Discounts table.  Then import those two into staging tables.  then insert the result of Purchases LEFT JOIN Discounts into the final table.

    Nothing to it.

    Case closed.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply