Forum Discussion

Excellove15's avatar
Excellove15
Iron Contributor
May 24, 2024

Power query transformations to lookup dates from one table into the other

Hi SergeiBaklan sir,

 

I have invoice detail table as below:

 

Also, I have EM - Misc Charge table that don't have date column:

 

But these 2 tables have 'DBName-Point_Id' , 'DBName-Id' & 'Charge' as a common columns.

I need to find the dates for each rows of 'EM - Misc Charge' table from invoice detail table for their corresponding 'DBName-Point_Id' , 'DBName-Id' & charge using power query transformations(upstream).

 

I am struggling to achieve this. we had a idea of appending these tables but that would increase the number of rows as we have millions of rows in our original data. This may cause a performance issue.

 

PFA file here Budget vs Accruals.pbix

Please let me know if you need further details

 

Thanks in advance!

 

BI & Data Analysis
developer
Macros and VBA
office 365
Power BI
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    May 25, 2024

    Excellove15 

     

    I need to find the Dates for each rows of 'EM - Misc Charge' table from 'Invoice Detail' table for their corresponding [DBName-Point_Id] , [DBName-Id] & [Charge]

     

    If you Merge tables 'EM - Misc Charge' with 'Invoice Detail' on the above common keys/columns Left Outer:

     

    then Expand the [Date] field from the [Invoice Detail] merged table:

    you should get what you want or am I missing something?

    • Excellove15's avatar
      Excellove15
      Iron Contributor
      May 29, 2024

      Hi Lorenzo 

       

      Apologise for the delay in getting back to you!

      Many thanks for this amazing solution!

       

      Just to confirm, 

      what if we wanted to combine both (EM - Misc Charge & Invoice Detail) tables into single table(all data from both table) by looking up at the dates from Invoice Detail table?

       

      Is it possible to append both tables and also lookup the date in a single step?

       

      Thanks in advance!

      Lorenzo SergeiBaklan 

      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        May 30, 2024

        Hi Excellove15 

         

        what if we wanted to combine both (EM - Misc Charge & Invoice Detail) tables into single table(all data from both table) by looking up at the dates from Invoice Detail table?

         

        To do it with the User Interface only:

        • Do a 1st merge as previously described
        • Do a 2nd merge (as a new query), selecting table 'Invoice Detail' first then table 'EM - Misc Charge' + their common keys & choose a Left Anti Join Kind

        • In the new/second query delete column [EM - Misc Charge] (contains Tables of nulls)
        • Append the 2 queries 

Resources