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

[sivarajan21]

Hi,

I have invoice detail table as below which has a date column:

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!

@Ahmedx @marcorusso @amitchandak @Greg_Deckler @jdbuchanan71 @Anonymous 

[jgeddes]

In theory you can merge the Invoice Detail table into the EM - Misc Charge table using the three common columns as the link. (Ctrl + click to select multiple columns.) 

It appears though in your data that the EMSQL_BWS lines in EM - Misc Charge table have multiple same entries so you will get a list of dates for each of those rows instead of a single value. In your data set provided this increases the row count of the EM - Misc Charge table from 109 to 418.

Hope this gets you pointed in the right direction.

[sivarajan21]

Hi @jgeddes ,

Thanks for these amazing solutions!

Just to confirm, before I accept this solution and close this query,

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!

[jgeddes · Accepted solution]

You can do that. 

For instance, if you wanted to add your EM - Misc Charge table (with dates) to the Invoice Detail table you could add this step to the Invoice Detail query.

combineTables = 
    Table.Combine(
        {
            #"Changed Type",
            Table.ExpandTableColumn(
                Table.NestedJoin(
                    #"EM - Misc Charge", 
                    {"DBName-Point_Id", "DBName-Id", "Charge"}, 
                    #"Changed Type", 
                    {"DBName-Point_Id", "DBName-Id", "Charge"}, 
                    "Invoice Detail", 
                    JoinKind.LeftOuter
                ), 
                "Invoice Detail", 
                {"Date"}, 
                {"Date"}
            )
        }
    )

 Again I note that this only works correctly if you have an unique key to build the join with. 
In the dummy set for an example if you look at 

There are four rows that are not unique. So they end up joining with 245 rows in the Invoice Detail table that are 'EMSQL_BWS-33524, EMSQL_BWS-159176, Standing_Charge'.

Hope this gets you pointed in the right direction.

Cheers.

[sivarajan21]

Hi @jgeddes ,

Thanks for your response! This is a dummy data set that i have provided which i described earlier.

So your solution will cause performance issues as there will be millions of rows after the merge in my original data set.(not this dummy 418)

Is there any other m-function that we can use to find the dates for rows? 

Or any other solution without merging the tables to get the dates?

Thanks in advance!

@jdbuchanan71 @Ahmedx @marcorusso @Greg_Deckler 

[jgeddes]

No problem, I understand your concern. 

For comparison sake I also ran a function in an add column step (code below)

Table.AddColumn(#"Changed Type", "addedColumn", (x)=> Table.SelectRows(#"Invoice Detail", each [#"DBName-Point_Id"] = x[#"DBName-Point_Id"] and [#"DBName-Id"] = x[#"DBName-Id"] and [Charge] = x[Charge]))

The merge queries version took 0.00:00:00.010246.

The add column function took 0.00:00:00.013154.

Merge queries version was slightly faster on the dummy set.

Hopefully one of the PQ gurus can optimize this further for you.

[sivarajan21]

Hi @jgeddes ,

Just to confirm, how did you calculate the timing of the queries that you mentioned above?

Could you please help me with that?

Thanks in advance!

[jgeddes]

In the tool ribbon I selected Diagnose Step.

In the last of generated diagnostic queries the total dutation is noted...

[sivarajan21]

Hi @jgeddes ,

Many thanks for your quick response!

I am able to figure out now!

Just grouped by query column and did the sum of exclusive during column😊

[sivarajan21]

Hi @jgeddes 

Thanks for your quick response!

Thats brilliant that you gave a comparison of timing between two.

Let me have a look and then get back!

Meanwhile lets see for other Power queries experts to have a look into this.

Thanks in advance!