Forum Discussion
Lookup another data in different queries
Hi,
I hope everyone is doing great!
I really need help on this.
I have 2 sets of data from different files;
1. Training ID with finance status
| Training ID | Finance Status |
| A00001 | Invoices Pending |
| A00002 | Invoices Pending |
| A00003 | Invoices Complete |
| A00004 | Back Charged |
| A00005 | Back Charged |
2. Training ID with learner details
| Training ID | Full Name | User ID |
| A00001 | Ainsley Brown | AIBR |
| A00001 | Greg Davis | GRDA |
| A00002 | Dennis Presley | DEPR |
| A00002 | Georgie Cook | GECO |
| A00003 | Eva Romero | EVRO |
| A00004 | Jessica Garland | JEGA |
| A00005 | Piers Greenwood | PIGR |
| A00005 | Joey Barber | JOBA |
| A00005 | Sandra Barnett | SABA |
I would like to lookup the finance status from the 1st query into the 2nd query to be a new column as I want the trainings to match with the learner details. Something that looks like this;
| Training ID | Full Name | Finance Status (new column) |
| A00001 | Ainsley Brown | Invoices Pending |
| A00001 | Greg Davis | Invoices Pending |
| A00002 | Dennis Presley | Invoices Pending |
| A00002 | Georgie Cook | Invoices Pending |
| A00003 | Eva Romero | Invoices Complete |
| A00004 | Jessica Garland | Back Charged |
| A00005 | Piers Greenwood | Back Charged |
| A00005 | Joey Barber | Back Charged |
| A00005 | Sandra Barnett | Back Charged |
I want the data to be shown as a table and also as a filter to differentiate the learner details based on the finance status.
I tried the formula below but it took too long to load probably because my source of data is huge.
Table.AddColumn(#"Renamed Columns", "Finance Status", each (let Session = [Training ID] in Table.SelectRows(#"First Query", each [Training ID] = Session)){0}[Finance Status])
Please help, thank you very much!
Hi harizothman
Add the the below lines right under you
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Training ID"}, #"finance status", {"Training ID"}, "finance status", JoinKind.LeftOuter),
#"Expanded finance status" = Table.ExpandTableColumn(#"Merged Queries", "finance status", {"Finance Status"}, {"Finance Status.1"})
in
#"Expanded finance status"Did it work ? Mark it as a solution to help spreading knowledge.
A kudos would be appreciated
1 Reply
Hi harizothman
Add the the below lines right under you
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Training ID"}, #"finance status", {"Training ID"}, "finance status", JoinKind.LeftOuter),
#"Expanded finance status" = Table.ExpandTableColumn(#"Merged Queries", "finance status", {"Finance Status"}, {"Finance Status.1"})
in
#"Expanded finance status"Did it work ? Mark it as a solution to help spreading knowledge.
A kudos would be appreciated