Conditional population of col in Table 1 from a col in Table 2 based on row / field value in Table 2

[TimMarsh]

Hi,

I hope the picture below explains what I am trying to do.

• Two tables: Table 1 a DIM (meta information about each item), Table 2 a FACT (for Referrals)
• On table 2 I have Advanced grouped by Referral ID, and Max (timestamp) to work out the max for each record, AND keep the original items.
• I then do a conditional colum so if creation timestamp = max (timestamp), set contents = Y (ie this is the MAX record for this item).
• I then need to add 2 colums from Table 2 (Col E and H for reference) to Table 1 by Referral ID but ONLY if Table 2 Max Record Flag = Y.

This is easy in QlikView in two steps (image below), but I am new to PBI/PQ (and haven't delved into M yet).

Yes, this ideally should be handled in our SQL source (e.g a view of Table 1 adding a col to indicate if a record is the max  for each Referral ID or inserting into existing table) but I am interested in solving in PBI for own understanding but also for prototyping (we have limited SQL dev availability which is a constraint here and on prototyping).

I could of course do this by creating a max record-only table of Table 2, use a conditional column to look for when max timestamp = timestanp then filter for max records, but this is an additonal table (plus as I said I'd like to understand how to do this a bit more elegantly).

Happy to park this in a function to be called from PQ.

Here's how to do it in QlikView for reference.

Thanks for any guidance!

[bhanu_gautam]

@TimMarsh , Try below steps

Go to Home > Transform Data to open Power Query Editor.
Load both Table 1 and Table 2.
Step 2: Group and Identify Max Records
Select Table 2.

Go to Home > Group By.

Group by Referral ID and add a new column for the maximum timestamp:

Group by: Referral ID
New column name: MaxTimestamp
Operation: Max on the Timestamp column.
Merge this result back with Table 2:

Go to Home > Merge Queries.
Merge Table 2 with the grouped table on Referral ID.
Expand the MaxTimestamp column.
Add a custom column to flag the max records:

Go to Add Column > Custom Column.
Name the column IsMaxRecord.
Use the formula: if [Timestamp] = [MaxTimestamp] then "Y" else "N".
Step 3: Filter Max Records
Filter Table 2 to keep only rows where IsMaxRecord = "Y".

[TimMarsh]

Hi! Thanks for replying, yep, I've done that in another model. However, I really would like to try and do without a potentially expensive merge if possible?

Left join will be on 18k records at present, which isn't huge, I know. Growing at 1k a month.

I thought there might be a way with a Table. SelectRows in M, but I just can't work it out.

I'm also cognisant of the fact I could just create a one to many relationship from T1 to T2 and then create a table that gets the Last Message Time and Content from T2 (after I do a Advanced group by, max timestamp, conditional fields to detect the latest record then create 2 new fields "last message time" and "last message status" - this works, but I feel like it's unecessary as you need to keep Max table you create with filters in the model and thus have to load it (model size?). Perhaps you're right and it would be better to make it an intermediary table for the max calcs then just merge....