Seventh Day of Christmas: Conditional Drill-through Button

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

Conditional Drill-through Button,

Verified User Selections, 

Dynamic titles,

Custom theme colors,

Appended queries,

Table expanded columns

and

a PDF in Power BI. 🎵

Drill-through

Some of you may love Power BI's drill-through functionality as much as I do. If you don't know what I'm talking about, drill-through enables users to right click on a data point and drill-through to a different page that automatically filters to show information only for that data point (usually at a more detailed level). It's a pretty cool feature, but still not widely known and even with helpful tooltips prompting users to "right click to drillthrough" it's not all that intuitive. What does "right click to drillthrough" actually mean? What value will it add? When do I need it?

Today's post is looking at a way to mimic this functionality in a way that guides the end user through the process. In order to make it work we will need to use: 

• DAX conditional measures (see yesterday's post on Verified User Selections if you missed the intro)
• Page Navigation Button
• Sync Slicers (we'll detail this in tomorrow's post)

In our SantaList report, we want users to be able to search for their own name and then check if they are on the naughty or nice list. Drill-through requires the user to right click a data point (such as on a pie, bar or line chart). It is already challenging enough to prompt the user to select one name from the slicer, we want them to just click a button and see their results.

Buttons

Today we will focus on the button part of this drill-through workaround. Power BI buttons are really powerful; their built-in functionality is amazing. In May 2020, Microsoft released new built in drill-through action for buttons that really helps go a long way in solving the problem of drill-through being too hard to discover. In our example though, it would require an extra click from our end users because drill-through is not activated by a slicer - they must select a data point (bar, pie, line, etc) in order to activate the button. This is just too much work! 

Lucky for us, Microsoft also introduced Conditional Page Navigation functionality for buttons in the same month. This is where the magic happens. It gives you the power to dream up any scenario and make it work in Power BI.

Conditional (fx) Page Navigation

Conditional formatting has worked its way into many areas of Power BI. It's not just used to apply pretty background colors to table cells anymore. In the Fifth Day of Christmas, we saw how to use SELECTEDVALUE in a measure to apply conditional formatting to our visualization titles. Well, we're going to use exactly the same approach here. Conditional formatting and DAX go hand in hand. 

Name the pages

Our report has three pages: 

• Home
• Check the List 
• Summary

We want users to navigate from the Home page to the other two pages. The trick: we want them to navigate to Summary page and 'See Santa's Entire List' if they have not selected exactly one name from the list. If they have selected exactly one name from the list, we want them to navigate to Check the List page and 'Check if <selectedname> has been naughty or nice'. 

Yesterday we created the [Button Text] measure that tells the user what will happen when they click the button. Today, we will create a [Page Navigation] measure that will enable the Conditional Page Navigation functionality. 

Create the DAX Measure

All we need to do to make this work, is create a measure that will return the name of the page exactly as it is written in our report. After yesterday's lesson on HASONEVALUE, this is easy: 

Page Navigation =
IF ( HASONEVALUE ( 'Santa List'[Name] ), "Check the List", "Summary" )

Note, the key here is that we have written the name of the pages in our report EXACTLY as they are written. 

Format the Button

Now we're ready to format the button. We will insert a blank button on the Home page. 

Expand the 'Action' section of the button. Set it to 'On' and 'Page navigation'. You'll notice here that you have the ability to select any of the pages in your report. That's already pretty cool, but we want this to be user directed, so click the fx next to Destination so we can add in the conditional formatting. Format by 'Field value' Based on field 'Page Navigation' and click OK. 

That's it! Now your button will take you to a different page depending on the value of that [Page Navigation] measure. To see how it works, expand the "Button text" section of the button and click the fx to conditionally format the button text using the same [Page Navigation] measure. 

The [Page Navigation] measure checks if the SantaList[Name] column has exactly one value, so we need to also insert a slicer for SantaList[Name]. 

Viola! Test out your button - make sure it takes you to the correct pages. If you get an error, check your spelling! Remember, you have to hold the Ctrl key to test buttons in Desktop (this isn't necessary in web service).

Finishing Touches

Okay, now explore some of the other formatting options for buttons - give them a background color. Center the text. Change the padding. 

Hide the 'Summary' and 'Check the List' pages - we don't want users finding them prematurely or if they haven't chosen the right slicer settings.

Pro tip: Hidden pages can still be accessed by report viewers, especially when printing to PDF. Do NOT use the hidden page functionality to hide incomplete work or messy testing ground pages. Do NOT use the hidden page functionality to hide sensitive or confidential data. Do use the hidden page functionality to make your report easier to read, use and navigate. 

Make it easy to get back to the Home page by adding a button to both the Summary and Check the List pages that say 'Return to Santa's Workshop' or something equally appropriate. These are easier - no conditional page navigation required. Just select 'Home' from the page navigation option. 

In the Twelfth Day of Christmas post we will step things up a level and use bookmarks to make this experience even better. 

How to: 

1. Open the SantaList.pbix file we have been working with.
2. You should have two pages: Page1 and Summary. 
3. Add a new page and call it "Check the List".
4. Rename Page1 to "Home".
5. Create the [Page Navigation] measure from above.
6. Click on the Home page.
7. Insert a Slicer visual for SantaList[Name] column.
8. On the Insert tab, click Buttons > Blank.
9. Expand Button Text in the Visualizations pane.
1. Turn it to 'On'
2. Click the fx next to the text box.
3. Select 'Field value' under Format by.
4. Select the [Button Text] measure we created yesterday under Based on Field.
5. Click OK.
10. Scroll down to 'Action' and expand this section in the Visualizations pane (the button should still be selected).
1. Select 'Page Navigation' for the action.
2. Click the fx next to the text box for 'Destination'.
3. Select 'Field value' under Format by.
4. Select the [Page Navigation] measure we created above under Based on Field.
5. Click OK.
11. Resize the Button so that the text fits.
12. Test the button - it should take you to the 'Check the List' page when 1 name is selected and to the 'Summary' page when more than one name is selected. 
13. Add a Button to the 'Summary' page:
1. Set Button Text to 'On' and type "Return to Santa's Workshop"
2. Set Action to 'Page Navigation' and Destination to 'Home'
14. Copy this button and paste on the 'Check the List' page.
15. Right click on 'Check the List' page and Hide.
16. Right click on 'Summary' page and Hide.
17. Save the file.

That's plenty for today. I know it doesn't seem like we have achieved a lot, and it's hard to see the full value in what we've done, but remember, Rome wasn't built in a day. Tune in again tomorrow for the next gift in the 12 Days of Christmas series where we'll use Sync Slicers to mimic that fantastic drill-through functionality in our intuitive button solution.

Sixth Day of Christmas: DAX HASONEVALUE()

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

Verified User Selections, 

Dynamic titles,

Custom theme colors,

Appended queries,

Table expanded columns

and

a PDF in Power BI. 🎵

Verify User Selections with DAX Information Functions

There are a few DAX functions that don't really perform any calculation on your data model, but instead simply tell you something about the current state of your data model, including row and filter context. These functions can be really helpful in verifying that your report user has done what you want or need them to, before allowing them to see the next page, number or calculation. 

Today we are going to see how the HASONEVALUE function can help verify that a user has made a single selection in a slicer, which is a common requirement in many business scenarios. In our example, we need to verify that the user has only selected one name before we can check Santa's list to see if that name is on the naughty or nice list. We will use the HASONEVALUE function to verify that they have only selected one name, and then change the button text or prompt when they have selected exactly one name.

DAX Function: HASONEVALUE()

HASONEVALUE is not a vital DAX function. It can be replaced with the True/False expression:

COUNTROWS(VALUES(<column>))=1

That is exactly what HASONEVALUE() does, it simply checks if there is exactly 1 row in the column. HASONEVALUE is a very simple function that takes only one argument: 

1. Column Name (required)

The function returns "True" if there is only one row in the column or "False" if there is more or less than one row in the column.

Okay, so we have lots of columns in our data, and in pretty much any dataset they will all have more than one row, so what good does this function do? It tells us information about the current filter context of the report. For those of you who know a bit about DAX, you'll know that Context is very important. For those of you who are new to DAX, the HASONEVALUE function let's us see what our data model looks like after it has been filtered, sliced and diced by all the report visualizations and interactions from the end user. 

In the table above we have used the HASONEVALUE function on the 'Santa List'[Name] column: 

Name HASONEVALUE =
    HASONEVALUE ( 'Santa List'[Name] )

We have put this in a table visualization with the 'Santa List'[Name] column. You can see that when the table provides a filter context of one name (aka one row), the HASONEVALUE function returns "True", but when we get to the Total row and no longer have that filter context, the HASONEVALUE function returns "False". 

We will get the same result if we put this in a card visual with a slicer for 'Santa List'[Name]: 

This is pretty cool, but not exactly helpful in creating a beautiful report, so information functions such as HASONEVALUE are often used in conjunction with other functions to add functionality. Today, we will use a tried and true IF() function to change the text based on the result of the HASONEVALUE function. 

Button Text =
IF (
    HASONEVALUE ( 'Santa List'[Name] ),
    "Click to see if " & SELECTEDVALUE ( 'Santa List'[Name] ) & " has been naughty or nice",
    "Click to see Santa's entire list"
)

Tip: Information functions that return "True" or "False" are a great partner for functions that require a 'logical test' as one of their arguments. 

You may have noticed that HASONEVALUE returns "False" if the column has been filtered for more  than one name. In our button text above, we have written "Click to see Santa's entire list". This should make it clear to the user that their filter selections won't carry through to the next page. If this does not meet your requirements, you might find the ISFILTERED function to be helpful too.

How to: 

1. Open the SantaList.pbix file.
2. Create the Button Text measure from above.
3. (Optional): Test it out using a table or card visual, or if you're feeling brave try a button but we'll learn how to make those tomorrow.
4. Save the report.

That's all you need to do for today. Tune in tomorrow for the next gift in the 12 Days of Christmas series when we'll put this measure to work as a page navigation button. 

Fifth Day of Christmas: DAX SELECTEDVALUE()

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

Dynamic titles,

Custom theme colors,

Appended queries,

Table expanded columns

and

a PDF in Power BI. 🎵

Dynamic Titles

Today's post is going to look at how I used the SELECTEDVALUE function to make a dynamic title for Santa's list. As you can see in the image below, the title of the table visualization updates based on the selection - if the "Naughty" pie section is selected, the title is "Santa's Naughty List", if the "Nice" pie section is selected, the title is "Santa's Nice List". If nothing is selected, it simply says "Santa's List". 

Nice and simple, but really impactful and helps your data storytelling reach the end user more effectively.

DAX function: SELECTEDVALUE()

SELECTEDVALUE is not an original DAX function, and so I had gotten used to doing things the long way without it. Now I use it all the time! So much so, that I've decided to introduce it in this 12 Days of Christmas series before its predecessor (aka the long way); HASONEVALUE(). 

SELECTEDVALUE() has two arguments:

1. Column Name (required)
2. Alternate Result (optional)

I must say I'm pretty pleased with the name Microsoft chose for this one - it does exactly what it says. SELECTEDVALUE function returns the current selected value of the column. Since we're talking about DAX, this of course considers the context of your report and data model. 

To see how this function works, I am going to create a fun little measure that uses the SELECTEDVALUE function to return a different title depending on what the users selects in my report: 

SantaListTitle =
"Santa's " & SELECTEDVALUE ( 'Santa List'[Naughty or Nice?] ) & " List"

Ultimately, I want this to update the title of my list visual as in the GIF above, but to see its behavior I am going to place it in a table visualization next to the 'Santa List'[Naughty or Nice?] column that I have used within the function:

Note I have left the second argument blank, and so nothing is returned in the total row. This makes my title easy to read. I could update this to say something like: 

SantaListTitle =
"Santa's " & SELECTEDVALUE ( 'Santa List'[Naughty or Nice?] , "Entire") & " List"

Note how the total row now displays "Santa's Entire List", when nothing is selected in the  'Santa List'[Naughty or Nice?] column. 

How to:

1. Open the SantaList.pbix that we've been working on. 
2. From the report view, click New Measure.
3. Create the SantaListTitle measure from above (choose the one you prefer or adapt for your own use).
4. Click the + to add a new page to your report. 
5. Rename the page "Summary".
6. Tick [Name] and [Naughty or Nice] from your table to create a table visualization.
7. Click the Format Paint roller icon.
8. Scroll down to find the 'Title' heading - Turn it on. 
9. Click the fx icon next to Title Text.
10. In the Title Text conditional formatting pop up box, select:
1. Format by: Field Value
2. Based on field: SantaListTitle
11. Click OK. 
12. Add a pie chart to the page with:
1. [Naughty or Nice] in Legend
2. Count of [Name] in Values
13. Select Naughty from the pie chart and watch the table title update.

We'll continue working with SELECTEDVALUE function in this report throughout the 12 Days of Christmas series. Tune in again tomorrow for the next gift in the 12 Days of Christmas series where we'll look at SELECTEDVALUE function's predecessor HASONEVALUE and discuss why it still has value over SELECTEDVALUE.

Fourth Day of Christmas: Report Themes and Design

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

Custom theme colors,

Appended queries,

Table expanded columns

and

a PDF in Power BI. 🎵

Customize Power BI Report Themes

Today's post is a little fun interlude. If you haven't been following the 12 Days of Christmas blog series, that's okay. We're not actually going to do anything with the data today. We are going to focus on making the report look pretty.

Power BI themes have been around for a long time, but they were previously really inaccessible. You needed to know JSON to be able to edit the theme. I actually taught myself a bit of JSON in order to increase the default font size on all my reports I used in class. Here's my attempt at a JSON larger font size theme that I wrote myself over a year ago: 

{ 

  "name": "GlobalLevelTemplate", 

        "dataColors": ["#107C10", "#A80000", "#0078D7", "#915203", "#D79A12", "#bb7711", "#114400", "#aacc66"],

        "background":"#FFFFFF",

        "foreground": "#3A6108",

        "tableAccent": "#568410",

  "visualStyles": { 

         "*": { 

             "*": { 

                 "title": [{ 

                     "show": true, 

                     "fontColor": { "solid": { "color": "#E0521f" } }, 

                    "background": { "no fill": { "color": "#FFFFFF" } }, 

                     "alignment": "center", 

                     "fontSize": 12, 

                     "fontFamily": "Arial" 

                 }], 

"values": [{

"fontSize": 12,

"fontFamily": "Arial"

}],

"rowHeaders": [{

"fontSize": 12,

"fontFamily": "Arial"

}],

"columnHeaders": [{

"fontSize": 12,

"fontFamily": "Arial"

}],

"categoryAxis": [{

"fontSize": 12,

"fontFamily": "Arial"

}],

"*": [{

"fontSize": 12,

"fontFamily": "Arial"

}],

                 "background": [{ 

                     "show": false,

                     "color": { "solid": { "color": "#FFFFFF" } }, 

                     "transparency": 25 

                 }], 

                 "lockAspect": [{ 

                     "show": true 

                 }], 

                 "border": [{ 

                     "show": false, 

                     "color": { "solid": { "color": "#565a5c" } } 

                 }], 

                 "visualTooltip": [{ 

                     "type": "Default" 

                 }], 

                 "stylePreset": [{ 

                     "name": "None" 

                 }] 

             } 

         }, 

         "page": { 

             "*": { 

                 "background": [{ 

                     "color": { "solid": { "color": "#FFFFFF" } }, 

                     "transparency": 50 

                 }], 

                 "outspace": [{ 

                     "color": { "solid": { "color": "#FFFFFF" } }, 

                     "transparency": 50 

                 }] 

             } 

         } 

     } 

 } 

Since April 2020, we don't have to waste time messing with JSON code and toiling over syntax errors because custom theme dialog went general availability. Let's look at what Power BI can do for us using the built in interface.

A picture is worth a thousand words, so I'll let you explore and play with this one on your own:

And here's the JSON output: 

{"name":"High Contrast","dataColors":["#107C10","#A80000","#0078D7","#5C2D91","#004B50","#0E00D7","#D83B01","#B4009E"],"background":"#FFFFFF","foreground":"#070f25","tableAccent":"#0F1934","textClasses":{"label":{"fontSize":16},"title":{"fontSize":20},"header":{"fontSize":20}},"visualStyles":{"page":{"*":{"outspace":[{"color":{"solid":{"color":"#107C10"}}}]}},"*":{"*":{"outspacePane":[{"checkboxAndApplyColor":{"solid":{"color":"#107C10"}}}]}}}}

Really? That's it?! Much more concise and elegant than what I had attempted, and infinitely easier to use. I'll leave you to tinker with the built in Theme Customizer. Pick some nice Christmas colors, your company brand colors, or your favorite colors. Remember to think about accessibility too. Worldwide, 8% of men and 0.5% of women are color blind, with red-green color blind being the most common. Even though red and green are a great Christmas combo, do not rely on them alone to distinguish data series in a pie or stacked column visual. 

Page Formatting

If you haven't discovered the Paint Roller icon in Power BI, find it now. It's in the Visualizations pane of your report and changes depending on what you have selected. If you want to format the report canvas (aka Page) then deselect all visuals and click the paint roller. 

Power BI reports default to a 16:9 page size as they are usually viewed from a computer screen, but you can change this (for example if you are creating a custom tooltip page). 

Page Background vs Wallpaper

Today we're going add some pizazz to our report with background images, but should we do this with Page background or Wallpaper? Examine the animation below and see if you can spot the difference between Power BI Wallpaper and Page background.

Power BI Wallpaper sits behind the report page. Note that when wallpaper transparency is at 0%, as we resize the page, the wallpaper stays fixed. This means that the slicer visual moves closer to Santa's beard. 

Power BI Page background changes the report page itself. Note that when Page background is at 0% transparency, there is a white strip under Santa when we resize the report page, and the slicer stays the same distance from Santa's beard.

I like to think of the Page background like shopping for stationery, whereas Wallpaper is more like shopping for an easel, or as Microsoft put it; wallpaper.

Tune in again tomorrow for the next gift in the 12 Days of Christmas series where we'll delve into DAX and see how the SELECTEDVALUE() function can add meaning and context to your reports in a dynamic way.

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. 

Second Day of Christmas: Expand Data tables in Power Query

Combine over 400 tables using Expand Data in Power Query

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

Table expanded columns

and

a PDF in Power BI. 🎵

There are many ways to combine and append data in Power BI. As we saw in yesterday's post, all our tables have the same format, so we don't need to do any transformations to them before the append. The source step of getting data from PDF lists all the tables, including their underlying data, in one big query. This means we can just expand the columns in the Source query to access all the data.

To do this, we need to go back a few steps to the Source step so we can see all the data in our PDF. 

Skip to the How To: section below to see step by step instructions.

As we noted before, Power BI recognises PDF tables and pages. We don't want to bring all the data in twice, so we'll just focus on the Tables for this example (they are typically tidier).

Pro tip: Filtering data is one of the first things you should do in Power Query with any dataset. It can speed up your refresh times and in some datasets supports a nifty thing called 'Query Folding' that basically enables Power BI to outsource a lot of the work (which ultimately speeds things up). 

This means two of the first steps you should do (even before the Changed Type step that Power BI automatically inserts) are:

1. Choose Columns
2. Filter Rows

Okay, so let's put that into practice. Start by deleting all the steps except 'Source'. 

Click Choose Columns from the Home tab in the ribbon. In this example, we only need to know what "Kind" of object and the "Data" within that object, so we'll select just these two columns:

Now we can filter for tables only: 

Wonderful, with the efficiencies out of the way, now we are free to focus on what we came here for; extracting that beautiful data from over 400 tables! Ok, so here's the fun part. So many data sources have embedded tables; databases, SharePoint lists, PDFs, Excel files, merged queries, .... I'm sure there's more that I can't think of at the moment. Basically, if you can learn how to access these embedded tables, and demystify the confusing interface that Power Query uses to display them, you will open endless opportunities for data analysis and exploration. 

This dataset is unique, in that it mashes the tables side by side and imports them as one query. The data we want is only two columns:

• Name
• Naughty or Nice

But when we try to expand the Data column to extract the columns from the tables, you'll see we have four columns:

• Column1
• Column2
• Column3
• Column4

Click Load more if you don't see all of these columns.

Expanding all four columns gives us Name, Naughty or Nice, Name, Naughty or Nice. Hang on, what's all this gibberish at the top?

Go back and look at the PDF if you need to - that's another valuable step when working in Power Query. Always understand your data source, what it means, where it comes from and how it might change. Now I can see that gibberish relates to the table of contents.

I really only want two columns, and I need to remove all the rows with table of contents (or anything that doesn't relate to the Naughty or Nice list).

I know Power BI does not allow me to simply cut and paste that data into the format I want. So what to do?

Today we will create two different queries: 

• SantaList1 for Column1 and Column2
• SantaList2 for Column3 and Column4

They will be exactly the same otherwise. Tomorrow we'll see how to combine them into one seamless table.

Okay, so we have two challenges: 

1. Select only Column1 and Column2.
2. Filter out the table of contents gibberish that we don't need (note I used that key word 'filter' again).

Pro tip: You can edit Applied Steps that you've already completed using the Settings cog. 

That means we don't have to start over, just click the settings cog for Expanded Data step, and deselect Column3 and Column4.

Finally, we can filter out that gibberish. How is this PDF likely to change? I know Column1 has all the names and there's no pattern to that data - each name is unique. What about Column2? What information do we expect to see in Column2 that is valuable to us for this report? 

"Nice" and "Naughty"

Anything else is useless to us. That's easy, just do a filter on Column2 to display only values equal to "Naughty" or "Nice":

Now we have to do that all over again with Column3 and Column4 right? Wrong! Tomorrow we'll see how to duplicate, edit and append this query to capture all the data in one table. 

How to:

1. Open the SantaList.pbix file you created on Day 1.
2. Click Transform Data to open the Power Query Editor.
3. Delete all steps except the Source step.
4. Rename the Query to SantaList1.
5. Click Choose Columns in the Home tab in ribbon.
6. Select Kind and Data. Click OK.
7. Filter the Kind column to show only Table.
8. Right click the Kind column and select Remove.
9. Click the double arrow at the Data column heading to Expand:
1. Tick Column1
2. Tick Column2
3. Untick Use original column name as prefix
4. Click OK.
10. Filter the Column2 column to show only "Naughty" and Nice".
11. Save the SantaList.pbix report. If asked, choose Apply Later.

That's enough for today. Tune in again tomorrow for the next gift in the 12 Days of Christmas series where we'll see how to quickly edit, duplicate and append queries. 

First Day of Christmas: Get Power BI Data from PDF

Get Naughty or Nice List Data from PDF (or web)

🎵 On the first day of Christmas, my true love gave to me... PDF data in Power BI. 🎵

What? You mean I don't have to convert to csv or copy/paste my data? That's right folks, Power BI connects directly to PDF files. How cool is that? You can follow along with me as I'm using data from the North Pole Government Department of Christmas Affairs. 

As you can see from the screenshot above, it's 461 glorious pages of, well, names! There are no column headings. When I highlight the table, it selects the left side first, then the right side. This is kind of handy for copy and paste - it keeps my names all stacked together. That is, until I get to the end of the page - then it selects the page header! What a mess. 

Copying and pasting the data above looks like this: 

Aerith Nice

Aerix Naughty

Aero Naughty

North Pole Government, Department of Christmas Affairs | Naughty and Nice List, 2020

13 | © Copyright North Pole Government 2020 christmasaffairs.com

Aeron Nice

Aeryn Naughty

Aeryn-Jessie Nice

Aesa Nice

Aeson Naughty

Aesyn Naughty

Aethan Naughty

Aetheria Nice

Aeva Nice

Aevlyn Naughty

Affie Nice

Afiba Nice

Afnan Nice

Afonso Naughty

Afrasenia Naughty

Africa Naughty

Afton Nice

Agastya Nice

Agata Nice

Agatha Nice

Aggie Naughty

Aghy Nice

Agianna Naughty

Agnes Nice

Agness Naughty

Agripina Naughty

Agusta Naughty

Agustin Nice

Agustina Nice

Agustus Nice

Ah Nice

Ahana Naughty

If I had to do this manually, I would render this data unobtainable. Lucky for us, PDF Connector has been available as General Availability in Power BI Desktop since April, 2019. 

When you first navigate to a PDF file in Power BI desktop, you'll notice Power BI picks up on each and every page, as well as each and every table. Good news about PDF files, they all started as something and typically have some nicely formatted tables that we can access. 

In this example we have over 400 tables. It appears to create a new table whenever there is a page break in the PDF. If you don't know how many rows or lines of text your data may have, this can make it challenging to write a query that will work for future data refresh cycles - how many tables do you need to select? What data will be in each one? 

Additionally, bringing these in as separate queries will be too time consuming. PDF data load is notoriously slow to begin with, and querying 400 tables individually will take ages. Santa will never make it on time!

Since I want all the tables in one query, I'm just going to select the first table for now and customize the results in Power Query. This trick is handy for many PDF files, as single datasets can often be split across tables when there is a page break in the PDF.

Pro tip: If you have only a few tables and want to import them all as separate queries, you can use the Shift key to select multiple files. Don't tick the box next to these tables. Just click on the text for the table name and Power BI will do the multi-select for you. If you tick the box, it has a tendency to skip the last table you select.

How to access local PDF file:

1. Save the PDF file to your desktop where you can find it easily. 
2. Open a new blank Power BI report.
3. In the Home tab, click Get Data > More.
4. Select PDF from the list.
5. Browse to find the Naughty and Nice list on your desktop.
6. Tick the box next to Table002 (the first table is not the data format we're looking for).
7. Click Transform Data to open Power Query - we still have some work to do.
8. Save the report as Santa List.
9. From the Query Settings pane, select the Source step and note the formula bar starts with =Pdf.Tables(
10. Examine the preview pane: 

You can see some very interesting information here - note I have clicked in the white space next to the yellow Table link for Table002. This gives me a preview of all the data that's in that table. 

Click in the white space next to Table003 - looks similar. Hmm, this is VERY interesting. Tomorrow I will demonstrate how we can extract all this information in one query. 

(Alternate) How to access PDF online:

If you don't want to download the PDF to your desktop, you can access it directly from the web URL instead. Power BI is smart enough to figure out it's a PDF, and will behave exactly the same as if you grabbed it from local desktop file.

1. Open a new blank Power BI report.
2. In the Home tab, click Get Data from Web.
3. Paste the following link: https://christmasaffairs.com/docs/Naughty-and-Nice-List-2020.pdf
4. Select Anonymous for the connection method.
5. Select the first table of names (Table002)
6. Click Transform Data to open Power Query.
7. Save the report as Santa List.
8. From the Query Settings pane, select the Source step. You'll notice the Formula bar starts with = Pdf.Tables( just as it did in the previous method.

That's all for today. 

Tune in tomorrow for the next gift in the 12 Days of Christmas series where we'll see a clever trick to access all the information in this entire 461 page document in one go. 

Data Story of the Month: Student Reports

Compare individual student performance to the entire population

This is for all my fellow teacher friends out there. It's the end of the year and I know you're busy marking final exams and preparing report cards. Some of you may even have the good fortune to be done already. For those of you who are still hard at work, I'm going to show you how to use Power BI to compare the results of one student to the entire distribution of your class. 

The Problem 

This problem was presented to me by @Dhuey on the Power BI Community forums.   

How can we easily compare one student's performance with the entire class? In this example, @Dhuey wanted to highlight the result of the selected student, but leave all other students' results visible, as you can see in the below image.

As you can see in the image above, Matrix 2, when we select an individual student, their results appear, but all other students' results are filtered out. This the expected behavior of slicer in Power BI. How then do we achieve the result of Matrix 1, with highlighting and keeping the results of all students?

This can be easily done in Power BI using a bit of DAX, conditional formatting, and a duplicate table. 

The Solution

Initial Setup: Get Data

You can follow along by pasting the code below into a New Blank Query in Power BI desktop: 

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZS7csIwEEV/JeM2eMaLX1KJ1KQPHUOXtDTA/2MJyTpeQdLYdzXrY+3VtU+n5tp10uya79v95/dy+wj60C6Xo/+q9FPOy819Zn3eRcYejKBdei5on5qnsP6UBssmM3ow+tLb433UhriEGIAYFOI1IQ+4IkYgRjTv0TwVNwxlQkxATEAMSqfnLMg2M2Yw5rJ97ogaLeuZGCBouCidz2rG+gqxgFg0BB89LF1tpL2JIR0T1pXJRUkMc1D7kE1Kpby9moaQVlOY01i8MnNUe6nSLoyqIF06JJusVrYwrYJA6MT/ETVhXGORT2KELzopXs/DxMbC4bvfaFIqWxhaQSR72PLfQExtLHDODllBaHXwhaGNhcMH2NbabnT+pTG1sXhD8QWSLVkg5wc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Student Code" = _t, #"Student Name" = _t, ENG_OG = _t, ENG_Tch = _t, HSS_OG = _t, HSS_Tch = _t, MAT_OG = _t, MAT_Tch = _t, SCI_OG = _t, SCI_Tch = _t]),

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Student Code", type text}, {"Student Name", type text}, {"ENG_OG", type text}, {"ENG_Tch", type text}, {"HSS_OG", type text}, {"HSS_Tch", type text}, {"MAT_OG", type text}, {"MAT_Tch", type text}, {"SCI_OG", type text}, {"SCI_Tch", type text}}),

#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Student Code", "Student Name"}, "Attribute", "Value"),

#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),

#"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Attribute.2]), "Attribute.2", "Value"),

#"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Attribute.1", "Subject"}, {"OG", "Result"}})

in

#"Renamed Columns"

This code will create a table of student results. I have kept it in the format the @Dhuey provided, so there are some nifty Power Query tricks in here using unpivot and pivot to tidy up the data into a report friendly format. All of this can be done with the click of a few buttons!

Rename this Query StudentData. 

Duplicate the StudentData query and call the second one StudentDataAll. (Right click the StudentData query in the Queries pane and select Duplicate).

Click Enter Data and paste the table below into Power BI. Call this DimResults.

Order	Result
1	A+
2	A
3	A-
4	B+
5	B
6	B-
7	C+
8	C
9	C-
10	D+
11	D
12	D-
14	F
15	NA

Pro tip: Create new tables and columns in Power Query (not DAX) to keep your slicers and report page loads more efficient.

Close and Apply the changes to your Power Query. 

Create Relationships

Now that we have all the tables loaded, we can address the all important concept of relationships in Power BI. In order to get the table to highlight but not filter when we select the slicer, we need to ensure there is no relationship between Student Code to Student Code, and that there is a relationship on Results. If you have named everything properly, Power BI should create the relationships for you: 

Create the Report

Now we can create the basic report. Build a report page with a slicer and a matrix.

Slicer:

Put StudentData[StudentName] in a slicer visual.

Matrix:

Create a matrix with: 

• DimResults[Result] in Columns
• StudentDataAll[Subject] in Rows
• StudentDataAll[Result] in Values > set the aggregation to COUNT

Note that we are using the StudentData table for the Slicer, but the StudentDataAll for the matrix. This means that the matrix won't update when we select a student in the slicer. 

Final Solution: The DAX

Ok, now for the cool part. How do we get the slicer to highlight based on the slicer selection? We'll use a bit of DAX and conditional formatting.

Create a new MEASURE: 

Count Result =

IF (
    HASONEVALUE ( 'StudentData'[Student Name] ),
    COUNTROWS (
        FILTER (
            'StudentDataAll',
            'StudentDataAll'[Student Code] = SELECTEDVALUE ( 'StudentData'[Student Code] )
        )
    ),
    0
)

What is this formula actually doing? 

HASONEVALUE() is checking if there is one student name in the filter/slicer selection. This ensures the table won't highlight everything when no students are selected.

COUNTROWS is counting the total results, and we have chosen to FILTER the StudentDataAll table as our table to count. You may recall that the StudentDataAll table is not connected to the StudentData table by Student Code, so when we filter for student, nothing happens. 

FILTER is checking to see what student has been selected, and only returns results that match the selected student. SELECTEDVALUE checks for the single value of student that has been selected by the slicer. Note this uses the StudentData table, as that is the table we put in our slicer above. 

Cool, so this creates a measure which updates based on the slicer selection, but the matrix still does nothing. 

That is pretty nifty, but now we need to apply the conditional formatting.

Conditional Formatting

The last step, click the down arrow next to Count of Result in your matrix, and choose Conditional Formatting > Background Color.

Use the new [Count Result] measure we just created to apply conditional formatting to the matrix.

Click OK.

Test it out. Download the completed Student Reports file to have a play. 

The Twelve Days of Christmas: Are you on Santa's Naughty List?

 Ever since I moved to New Zealand, it has been a struggle to get in the Christmas mood- summer Christmas just doesn't feel like Christmas. So this year I thought I'd try to find some festive examples to get in the holiday spirit. 

As part of the 12 Days of Christmas, I will demonstrate how to use Power BI to create the report above, helping Santa keep track of who has been naughty and who has been nice. I have used a few techniques to make this work:

• Day 1: Get data from PDF
• Day 2: Expand table data
• Day 3: Append queries
• Day 4: Customize report page wallpaper and report theme
• Day 5: DAX measures using SELECTEDVALUE() to customize text and report based on slicer selections
• Day 6: DAX measures using HASONEVALUE() to add conditions and checks to your report
• Day 7: Buttons with Page Navigation action using conditional formatting
• Day 8: Sync Slicers across report pages
• Day 9: DAX measures using SWITCH() to add meaning to a page
• Day 10: Custom visual Comicgen to add emotion to your data
• Day 11: Custom visual Enlighten Data Story to add text and context to your data
• Day 12: Bookmarks to reset filters and improve end user experience

So check your Naughty or Nice status in the report above, and tune in on Christmas Day for the first post in the series. Subscribe to this blog so you don't miss future updates.