Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
You would need to provide data to really help, but the general process would be this:
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYou would need to provide data to really help, but the general process would be this:
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reportingthanks @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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi, 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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.