Skip to main content
cancel
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

Reply
michaelu1
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.

michaelu1_0-1709137147469.png


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

michaelu1_1-1709137166062.png

 

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:

michaelu1_2-1709138407375.png

 

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: https://drive.google.com/file/d/1N5B0eunp2ZyfqOD0a2OkXybADhM9Vr4I/view?usp=drive_link

2 REPLIES 2
amitchandak
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: https://youtu.be/-mThmfSaiV8

@amitchandak 
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

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

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