Merge the first record of child table

[marktvc]

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.

[edhans · Accepted solution]

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. 

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.

[marktvc]

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"))

[edhans]

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.

[benhoward00]

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.