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.