Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello,
I have 2 tables I'd like to join. The secondary (table B) should fill the primary (inserting rows) when merging with the priamry table (Table A). I'm having trouble because i need to scan through 2 fields from the second table, and merge it with the primary where it does not exist. See example below
I do a right outer join on project key, and I'm getting my rows multiplied by 5 (5,000 total records).
Can anyone please help with this??
Solved! Go to Solution.
Hi @Anonymous ,
One sample for your reference, please check the following steps as below.
1. Add custom columns in both tables like this.
[Products]&[Severity]
2. Merge tables (LeftOuter) and fill the date column down.
M code for your reference.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTylNLlFwVNJRCk4tSy3KLKlUMFSK1cEhZYRbyhi3lAmKlBNuu5xw2+WE2y4nNLtiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Products = _t, Severity = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Products", type text}, {"Severity", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Products]&[Severity]), #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Custom"}, TableA, {"Custom"}, "TableA", JoinKind.LeftOuter), #"Expanded TableA" = Table.ExpandTableColumn(#"Merged Queries", "TableA", {"Products", "Date"}, {"TableA.Products", "TableA.Date"}), #"Filled Down" = Table.FillDown(#"Expanded TableA",{"TableA.Date"}) in #"Filled Down"
PBIX as attached.
Hi @Anonymous ,
One sample for your reference, please check the following steps as below.
1. Add custom columns in both tables like this.
[Products]&[Severity]
2. Merge tables (LeftOuter) and fill the date column down.
M code for your reference.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTylNLlFwVNJRCk4tSy3KLKlUMFSK1cEhZYRbyhi3lAmKlBNuu5xw2+WE2y4nNLtiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Products = _t, Severity = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Products", type text}, {"Severity", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Products]&[Severity]), #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Custom"}, TableA, {"Custom"}, "TableA", JoinKind.LeftOuter), #"Expanded TableA" = Table.ExpandTableColumn(#"Merged Queries", "TableA", {"Products", "Date"}, {"TableA.Products", "TableA.Date"}), #"Filled Down" = Table.FillDown(#"Expanded TableA",{"TableA.Date"}) in #"Filled Down"
PBIX as attached.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
73 | |
72 | |
39 | |
31 | |
26 |
User | Count |
---|---|
97 | |
87 | |
43 | |
40 | |
35 |