cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
marktvc
Helper I
Helper I

Merge the first record of child table

Hi All,

 

I'm looking to merge to tables, but only pull the first matching record from the child table.

 

I can complete this by a DAX look up using

Insurer =
CALCULATE(FIRSTNONBLANK('Get_LineHeader'[IssuingCompanyLookupCode],TRUE()), FILTER('Get_LineHeader','Get_LineHeader'[UniquePolicyIdentifier]=Get_PolicyHeader[UniquePolicyIdentifier] ) )
 
There is a one to many record (Parent to Child) and I only need the first instance of the child.
 
 
1 ACCEPTED SOLUTION
edhans
Super User
Super User

You would need to provide data to really help, but the general process would be this:

  1. Define what "first" means. Earliest date for example. The order of records in Power Query genreally isn't meaningful and they can and will swap positions when you start merging, appending, etc. It isn't like row numbers in Excel.
  2. Merge the table using a left outer join.
  3. Add a column using Table.Min based on your criteria. For example, see below:
  4. edhans_0-1615220532021.png

     

The Table Merge has all 3 records. But the "Earliest Record" column is using Table.Min([Table], "Date") so it only pulls the earliest record from that 3 record table. Then you hit the Expand icon in the upper right of the Earliest Record field and you have only that earliest record. You can expand as many fields as desired, all, or just one, or any combination.

 

Then remove the Table column that was merged.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

4 REPLIES 4
edhans
Super User
Super User

You would need to provide data to really help, but the general process would be this:

  1. Define what "first" means. Earliest date for example. The order of records in Power Query genreally isn't meaningful and they can and will swap positions when you start merging, appending, etc. It isn't like row numbers in Excel.
  2. Merge the table using a left outer join.
  3. Add a column using Table.Min based on your criteria. For example, see below:
  4. edhans_0-1615220532021.png

     

The Table Merge has all 3 records. But the "Earliest Record" column is using Table.Min([Table], "Date") so it only pulls the earliest record from that 3 record table. Then you hit the Expand icon in the upper right of the Earliest Record field and you have only that earliest record. You can expand as many fields as desired, all, or just one, or any combination.

 

Then remove the Table column that was merged.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

thanks @edhans   This was close enough for me to get the required answer.  As dates across all child are the same (computer generated), I'm liminted to using the UniqLine key in the table called "Raw_Line".  Below is the slightly modified version.

 

= Table.AddColumn(#"Merged Queries", "Custom", each Table.First([Raw_Line], "UniqLine"))

Glad I was able to help. A suggestion - before executing this code, add Table.Buffer() to the step above this one. I don't know what your full query looks like, but it is possible that upstream merges, indexes, etc. can change the order of the table unexpectedly. Table.Buffer() mitigates this.

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
benhoward00
Frequent Visitor

Hi,  Not sure about the DAX, but in Power Query I would duplicate the child table, and then remove duplicates based on the UniquePolicyIdentifier column.   That should then only return the 1st (and only) instance of the child.

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors