Forum Discussion
Lookup values in other query
Hi All,
hopefully someone can guide me in the right direction?
i have a query whcih results in this data like this:
firstName | lastName | course | attendedSessionOne | |
joe | bloggs | [email protected] | maths | no |
adriana | frank | [email protected] | english | yes |
jaci | sadler | [email protected] | science | no |
kirk | gregory | [email protected] | science | no |
brandie | tatum | [email protected] | maths | yes |
mortimer | riley | [email protected] | 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 | course | attendedSessionTwo | |
joe | bloggs | [email protected] | maths | yes |
jaci | sadler | [email protected] | science | no |
kirk | gregory | [email protected] | science | yes |
mortimer | riley | [email protected] | 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 | course | attendedSessionOne | attendedSessionTwo | |
joe | bloggs | [email protected] | maths | no | yes |
adriana | frank | [email protected] | english | yes | no |
jaci | sadler | [email protected] | science | no | no |
kirk | gregory | [email protected] | science | no | yes |
brandie | tatum | [email protected] | maths | yes | no |
mortimer | riley | [email protected] | 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
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
3 Replies
- PhilipTreacy
Super User
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
- 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
- PhilipTreacy
