Lookup another data in different queries

[harizothman]

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!

[DataVitalizer · Accepted solution]

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