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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply

Merge queries with first with the first combination of second table

Hello everyone!

 

I have two tables.

 

At first, I have a series of dates, event codes and names that are repeated for periods.

For example:

01/01/2021 - Code A - Name A

01/01/2021 - Code A - Name B

01/01/2021 - Code A - Name C

01/02/2021 - Code B - Name A

01/02/2021 - Code B - Name B

01/02/2021 - Code B - Name C

 

In the second table, I have new entries with dates, codes and the names. In this table, the same name can be repeated more times for the same code but with different dates.

For Example:

 

01/03/2021 - Code A - Name A

01/04/2021 - Code A - Name A

01/03/2021 - Code A - Name B

01/04/2021 - Code A - Name C

01/04/2021 - Code B - Name A

01/05/2021 - Code B - Name B

01/07/2021 - Code B - Name B

 

What I need? 🤔

 

I need to merge these tables, bringing to the first table, the first date for the code and name combination of the second table. 

For example:

 

01/01/2021 - Code A - Name A - 01/03/2021

01/01/2021 - Code A - Name B - 01/03/2021

01/01/2021 - Code A - Name C - 01/04/2021

01/02/2021 - Code B - Name A - 01/04/2021

01/02/2021 - Code B - Name B - 01/05/2021

01/02/2021 - Code B - Name C - null

 

All duplicates that exist in the second table should be ignored, as I only need to bring the first date (the smallest date) of that code + name for the reference of the first table.

 

If possible, I can count how many times this combination appears in the second table?

 

I tried to exclude duplicates, but it didn't work out, since there were still duplicate values ​​due to different dates. 😞

1 ACCEPTED SOLUTION
Anonymous
Not applicable

another way, without group by, could be this:

 

let
    Origine = Table.TransformColumns (Table.NestedJoin(T1,"codde-name",T2,"codde-name","tt",JoinKind.FullOuter),{"tt", (t)=>t{0}[date]}),
    #"Tabella tt espansa" = Table.ExpandTableColumn(Origine, "tt", {"first"}, {"tt.first"})
in
    #"Tabella tt espansa"

 

where T1 and T2 are:

 

 

 

image.png

 

 

image.png

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

image.png

 

 

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyNDBV0F5/yUVAVHIMMvMRfIUIrVwavAiZACZ7gCIxQFTphW4FDgREgB0IpYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Colonna1 = _t]),
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"Colonna1", type text}}),
    #"Suddividi colonna in base al delimitatore" = Table.SplitColumn(#"Modificato tipo", "Colonna1", Splitter.SplitTextByDelimiter(" - ", QuoteStyle.Csv), {"Colonna1.1", "Colonna1.2", "Colonna1.3"}),
    #"Modificato tipo1" = Table.TransformColumnTypes(#"Suddividi colonna in base al delimitatore",{{"Colonna1.1", type date}, {"Colonna1.2", type text}, {"Colonna1.3", type text}}),
    #"Rinominate colonne" = Table.RenameColumns(#"Modificato tipo1",{{"Colonna1.1", "date"}, {"Colonna1.2", "code"}, {"Colonna1.3", "name"}}),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Rinominate colonne", "date2", each List.Min(Table.SelectRows(Tabella2, (r)=>r[code]=[code] and r[name]=[name])[date]))
in
    #"Aggiunta colonna personalizzata"

Apparently it worked, but it got really, really slow. I have a large volume of data, about 400 thousand lines. That's right?

Anonymous
Not applicable

another way, without group by, could be this:

 

let
    Origine = Table.TransformColumns (Table.NestedJoin(T1,"codde-name",T2,"codde-name","tt",JoinKind.FullOuter),{"tt", (t)=>t{0}[date]}),
    #"Tabella tt espansa" = Table.ExpandTableColumn(Origine, "tt", {"first"}, {"tt.first"})
in
    #"Tabella tt espansa"

 

where T1 and T2 are:

 

 

 

image.png

 

 

image.png

Anonymous
Not applicable

well ... you didn't report this little detail. try like this:

image.png

 

 

group tab2 with respect to code-name and take the first element of each group

 

image.png

 

at this point you can do what the topic title says

 

image.png

 

@LeandroCampacci  if you can do the transformation in DAX completely you will see a huge performance boost

Column = CALCULATE(MINX(FILTER(t2,t2[name]=MAX(t1[name])&&t2[code ]=MAX(t1[code ])),t2[date]))

Capture.PNG

 

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

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