Lookup values in other query

[7jaydub9]

Hi All,

hopefully someone can guide me in the right direction?

i have a query whcih results in this data like this:

firstName	lastName	email	course	attendedSessionOne
joe	bloggs	j.gloggs@example.com	maths	no
adriana	frank	a.frank@example.com	english	yes
jaci	sadler	j.sadler@example.com	science	no
kirk	gregory	k.gregory@example.com	science	no
brandie	tatum	b.tatum@example.com	maths	yes
mortimer	riley	m.riley@example.com	english	yes

So the above would show a course and who attended the fisrt session

I then also have a query that shows the information to session 2 as follows:

firstName	lastname	email	course	attendedSessionTwo
joe	bloggs	j.bloggs@example.com	maths	yes
jaci	sadler	j.sadler@example.com	science	no
kirk	gregory	k.gregory@example.com	science	yes
mortimer	riley	m.riley@example.com	english	no

What i'm trying to achieve is to add a column in the first query that looks up the course and the identifier for the person (email address) from the second table and say if they attended the second session or not - if they don't appear in the second query, then the default should be no.

So, looking at the above two queries, i should have a final peice of data that looks like the below:

firstName	lastName	email	course	attendedSessionOne	attendedSessionTwo
joe	bloggs	j.gloggs@example.com	maths	no	yes
adriana	frank	a.frank@example.com	english	yes	no
jaci	sadler	j.sadler@example.com	science	no	no
kirk	gregory	k.gregory@example.com	science	no	yes
brandie	tatum	b.tatum@example.com	maths	yes	no
mortimer	riley	m.riley@example.com	english	yes	no

Hopefully i'm exaplining it OK - please let me know if not and i'll provide more information if i can.

Cheers,

J

[PhilipTreacy]

@7jaydub9 

No worries 🙂

[PhilipTreacy · Accepted solution]

Hi @7jaydub9 

Download PBIX file with the example shown below

With those 2 queries (tables) in Power Query, you can merge them and pull the attendedSessionTwo column into Query1.

Immediately after merging the new column will contain a Table. You just expand these tables (click the double headed arrow at the top of the column) and only extract the attendedSessionTwo column

Where there are peopel who did not atend Session 2, you'll have a null. If you right click on the column header and choose Replace value you can replace these nulls with no

Regards

Phil

[7jaydub9]

Seems so simple when you explain it like that, but guess i still have a lot to learn. Many thanks for this, really appreciate it. J