Third Day of Christmas: Append Queries

Append Queries in Power BI

🎵 On the third day of Christmas, my true love gave to me... 

Appended queries,

Table expanded columns

and

a PDF in Power BI. 🎵

If you have been following this 12 Days of Christmas blog series, you will have also noticed that the Naughty or Nice list of data we are using has two names per row. Yesterday we decided to focus only on the first two columns. 

That leaves us today with the challenge of extracting the information from the last two columns, and combining all data into one table with only two columns. There are a few ways we could do this, I am going to show you how to do it without any custom coding required. 

Task 1: Duplicate and Edit Query

Our first task for today is to duplicate the SantaList1 query that we created yesterday. Simply right click on the SantaList1 in the Queries pane and choose Duplicate.

This is absolutely one of my favorite features of Power Query. Here's a few reasons why:

• There's no 'undo' button in Power Query, so if I want to mess with a query, I'll duplicate it first as a backup. Only when I have everything working will I delete that duplicate backup.
• Data is full of patterns, and duplicating queries lets me take advantage of those patterns. I can repeat the same steps with minor changes VERY quickly using Duplicate queries.
• Quite simply, how can you not love a copy paste function?

Okay, now for the minor changes. We need to edit this Query so that it expands Column3 and Column4. Do we remember where that step is?

Pro tip: Rename your Applied Steps and use the Properties to add descriptions to make your Applied Steps easier to understand. Do this for all steps that you think are either ambiguous or important. 

You never know when or what you'll need to edit later, so take the time to make good notes for your future self. Trust me, it's worth your time. As you can see in the image below, descriptions show up when you hover your mouse over the name of the Applied Step:

Click the Settings Cog next to the Expanded Data step, select Column3 and Column4 (deselect Column1 and Column2).

Click on Filtered Rows1 step - looks like we have an error to resolve. Since we're going with the no code method, let's just delete this step and add it back in. Again, notes to yourself can help immensely here so that you don't have to guess what you were trying to achieve at this stage of the query.

We were removing the gibberish (in this case null) table of contents data. 

Hang on, is that truly what we want? To remove anything that's null? Actually, if we're being precise (and we should ALWAYS be precise when working with Power BI) we want to KEEP anything that says "Naughty" or "Nice" and ONLY rows that say "Naughty" or "Nice". 

So let's do a more explicit filter this time. Click the filter arrow on Column4 and select Text Filters > Equals. You should get a Filter Rows box appear: 

Pro tip: Know your data, and be precise with your query. This will ensure it is future proof and refreshes properly even with changes in the data source.

How to duplicate and edit query:

1. Open the SantaList.pbix file from yesterday. 
2. Right click on SantaList1 in the Queries pane and Duplicate.
3. Rename this new query to SantaList2.
4. Click the Settings cog next to the Expanded Data step to edit.
5. Untick Column1 and Column2 and tick Column3 and Column4.
6. Filter Column4 using Text Filters > Equals
1. equals Naughty
2. Select Or
3. equals Nice
4. Click OK
7. Save the file. Apply Later.

Task 2: Append Queries

Our second task for today is to append SantaList1 and SantaList2 into one table. 

What does append actually mean? If you look carefully at the image above, you'll notice that two tables are being combined on top of each other. Power BI will match the column names in each table, and combine into one big table. 

I love overcomplicating things, and learning from my mistakes. I'm also a firm believer in seeing things first hand, so just for fun, let's see what happens if we don't rename the column headings. Choose SantaList1 and SantaList2 for your append queries. Scroll up and have a look at the GIF at the top of this blog to see what happens. We end up with a new query that has four columns. Well that's silly, I though we were trying to get rid of the four column structure of our data?

Right, so we need to understand how append works - it looks at the column names of each table, and if there is a column with the same name in the other table, it combines them into one long column. It does this all the way across the table. So what happens with the left over columns that don't have matching names? Good news - they don't disappear. As you can see in the GIF at top, all columns are preserved. This is my number one indicator that my data didn't have an exact match when I did an append:

1. Check total number of columns
2. Append Queries
3. Confirm total number of columns hasn't changed

How to Append Queries (the right way):

1. Select SantaList1.
2. Rename Column1 to "Name" and Column2 to "Naughty or Nice?"
3. Set Data Type to Text.
4. Select SantaList2.
5. Rename Column3 to "Name" and Column4 to "Naughty or Nice?"
6. Set Data Type to Text.
7. In the Home tab, click Append Queries as New.
8. Select Santa List 1 and Santa List 2.
9. Rename this Query to Santa List.
10. Right click on Santa List 1 in the Queries pane and choose Disable Load. Do the same with Santa List 2. We don't need these in our report since they only tell half the story. We'll use the complete list we created in the append.
11. Close and Apply the changes.
12. Save the file.

Hurray! Pat yourself on the back. You've finished everything in Power Query. The data model is now complete for this report. That's plenty for today. Tune in again tomorrow for the next gift in the 12 Days of Christmas series where we'll have some fun exploring Power BI report themes, colors and background photos.