Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello Gurus,
I have 2 tables in direct query (Table A & Table B). Table is joined with Id, Now I need to compare 2 tables based of date and show that lastest date row as output.
Table A
Id | Name | Sal | Date |
1 | A | 60 | 8.1.2019 |
2 | B | 80 | 8.1.2019 |
3 | C | 50 | 7.1.2019 |
Table B
Id | Name | Sal | Date |
1 | A | 70 | 9.1.2019 |
2 | B | 50 | 7.1.2019 |
3 | C | 70 | 9.1.2019 |
Output : Based on the max date compared between two tables, I need to show the records
Id | Name | Sal | Date |
1 | A | 70 | 9.1.2019 |
2 | B | 80 | 8.1.2019 |
3 | C | 70 | 9.1.2019 |
Thanks in Advance,
Shamar
Solved! Go to Solution.
Hi @Anonymous
You may use 'Merge queries' and then add condition column to get the latest date.Attached the sample file for your reference.
let Source = Table.NestedJoin(TableA,{"Id"},TableB,{"Id"},"TableB",JoinKind.LeftOuter), #"Expanded TableB" = Table.ExpandTableColumn(Source, "TableB", {"Sal", "Date"}, {"TableB.Sal", "TableB.Date"}), #"Added Conditional Column" = Table.AddColumn(#"Expanded TableB", "Custom", each if [Date] > [TableB.Date] then [Date] else [TableB.Date]), #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Custom.1", each if [Custom] = [Date] then [Sal] else if [Custom] = [TableB.Date] then [TableB.Sal] else null), #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column1",{"Sal", "Date", "TableB.Sal", "TableB.Date"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Date"}, {"Custom.1", "Sales"}}) in #"Renamed Columns"
Regards,
Cherie
Hi @Anonymous
You may use 'Merge queries' and then add condition column to get the latest date.Attached the sample file for your reference.
let Source = Table.NestedJoin(TableA,{"Id"},TableB,{"Id"},"TableB",JoinKind.LeftOuter), #"Expanded TableB" = Table.ExpandTableColumn(Source, "TableB", {"Sal", "Date"}, {"TableB.Sal", "TableB.Date"}), #"Added Conditional Column" = Table.AddColumn(#"Expanded TableB", "Custom", each if [Date] > [TableB.Date] then [Date] else [TableB.Date]), #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Custom.1", each if [Custom] = [Date] then [Sal] else if [Custom] = [TableB.Date] then [TableB.Sal] else null), #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column1",{"Sal", "Date", "TableB.Sal", "TableB.Date"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Date"}, {"Custom.1", "Sales"}}) in #"Renamed Columns"
Regards,
Cherie
Hi V-chech-msft,
Thanks for your solution, There is other part in my scenario is
if ( TableA. Date > TableB.Date )
then - > show all columns from TableA
else -> show all columns from Table B
How to achieve this?
Thanks,
Shamar
Hi @Anonymous
I would suggest you create a new thread on forum so that more community members can see it and provide advice. Please remember to post dummy data and desired result.
Regards,
Cherie
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
86 | |
84 | |
66 | |
62 | |
59 |
User | Count |
---|---|
197 | |
116 | |
107 | |
77 | |
70 |