Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III
Helper III

How to conditionally expand a merged query

I have 2 tables:
One has a field of Resident IDs and next to each tenant it shows who the prior Resident ID was.


The other table has a list of charges for each Resident ID.



I'm trying to join on the overall Unit ID and then expand the new column twice: once where the resident ID equals the current ID and then again where the resident ID equals the prior ID, like below:



How do I add a column where it conditionally expands the joins table?

 On the attached file, Leases_2 shows the expected result. I got there by joining twice to the charges table on the resident IDs. I assume that it is a less efficient method as my charges table contains over 1M rows and has a number of transformations, so I assume its more efficent to join it once and then expand twice instead.


Here is the file:

Super User
Super User

@michaelu1 , In this case, you have to join twice.


Or You can try the function Power BI- Power Query Table.AddJoinColumn:

Why can't I conditionally expand? I've done it before using M code like this: 

= Table.AddColumn(#"Expanded GroupAllDate","GroupDelqDate", each Table.Max(Table.SelectRows([ALL], each [Delq Type] = "Delq" or [Delq Type] = "ATCF"), "LastDateTime"))


The only thing I can't figure out is how to reference the original query to compare the 2 columns.


Assuming I use Table.AddJoinColumn would that need to cause the joinded table to load twice?

Helpful resources

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.


Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors