Multiple sources in same Query
I’ve faced this scenario so often, it’s time for a blog post about it! The issue is that sometimes the data I want in my table is split into multiple sources. Here’s a sample example with only 10 rows for simplicity. I have users and meta data on each user. All good, right?
Wrong. It’s not good. I have more meta data, but for some reason that can only be found in a different table! The users location data can be found here.
Sure, I could add 2 queries like this, getting all the data into my report. From here I could relate my tables so any time I want location data I use one table and the rest is found in my main table.
Let’s not.
Another solution is that we simply merge the data in Power Query. This looks much better!
I’ll expand the 2 location fields like this and voilá! Much better looking table!
There’s only one issue now.. I’m left with a table that doesn’t really do anything. I have all data in the Users table so the Users Location can be removed. And sure, I can hide it in the report view from here, but what if there was a cleaner way?
Merging the queries into one!
Let’s begin by copy the code from the advanced editor of the “User Location”.
OK now let’s head into the advanced editor of our main query instead. In here, we merge with the location. For now, let’s keep that.
Now let’s make some manual edits and rewrite this.
Have a look at these sections of the M code. They’re the important ones 😉
“UserLocation = ” is my new defined step after “User_Table”. That was written manually. After than, I pasted the M code form the User Location-Query. Since the query now continues, I added the comma on line 14 and finally on row 17 I manually switched what Table I wanted to merge with. Instead of the separate query we had before, I now simply reference UserLocation (or whatever you name your step on row 7).
And there you have it! We only have 1 Query, we only have 1 data table in the report view, but we have 2 sources that are combined in the same Query so that we get a clean and full User table!
This is a useful method when you want to merge or append tables! Good luck!
About The Author
