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
zhoura345
New Member

Help with Merging Two Tables to Get Desired Result

I have two datasets (one with 5k+ rows, the other 1k+ rows) already loaded in the Power Query Editor. For simplification here are two sample tables to show what I'm looking to do: 

 

Table 1: 

Table1.png

 

 

 

 

 

Table 2: 

zhoura345_0-1704239918142.png

I need a way to merge the two such that I get this result: 

zhoura345_1-1704240032986.png

None of the default joins available in the Merge Queries seems to be able to get me this. The Left Outer join gives me all values in the Volume column since for every Trip# in Table1, there is a match in Table2. 

 

What can I do to get this desired result? Thank you so much!

1 ACCEPTED SOLUTION
Ahmedx
Super User
Super User

6 REPLIES 6
Ahmedx
Super User
Super User

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

Ahmedx
Super User
Super User

pls try this

Screenshot_4.png

Thank you! This worked very well. If you don't mind me asking, my original dataset has many columns, and they seem to be gone after the GroupBy action. Is there a way to return them back? 

at the “Extended” step, you must check all columns

Screenshot_2.png

ryan_mayu
Super User
Super User

@zhoura345 

try to create index column in both table and create rank column

= Table.Group(#"Added Index","Trip",{"a", each Table.AddRankColumn(_,"rank",{"Index",1})})

11.PNG

then when you merge two table, both matching trip column and rank column

12.PNG13.PNG

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRUitXBoIwglDE6FQsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Trip = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Trip", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    Custom1 = Table.Group(#"Added Index","Trip",{"a", each Table.AddRankColumn(_,"rank",{"Index",1})}),
    #"Expanded a" = Table.ExpandTableColumn(Custom1, "a", {"rank"}, {"rank"}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded a", {"Trip", "rank"}, #"Table (2)", {"Trip", "rank"}, "Table (2)", JoinKind.LeftOuter),
    #"Expanded Table (2)" = Table.ExpandTableColumn(#"Merged Queries", "Table (2)", {"Volumn"}, {"Volumn"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table (2)",{"rank"})
in
    #"Removed Columns"

pls see the attachment below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thank you for taking the time to explain the steps. This was so easy to follow and I was able to replicate the result with the simple dataset. However, when I attempted to use the same method on my original big dataset, it returned all null values. 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Fabric Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.