Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |