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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
michaelu1
Helper II
Helper II

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.