Power BI Blog: Excel-sior - Part 1

Welcome back to Power BI Tips.

Last week’s we looked at the myriad of ways data can be import into Power BI, now it is time to bring some in for analysis. This blog series will be using case studies of real data and we'll go through the steps from Get Data, cleaning data in the query editor, creating a measure wax DAX and then building a simple visualisation. We will be using free data that is available online for easy replication of the results.

Excel files are the format that most people use to store and analyse flat data. Simple formula calculations and the ability to visually format data. Today’s file comes from NASA in the quest to go ever upward! First save this file in an easily accessible place on your computer (note we had to make slight adjustments to the original data, the errata is at the end of this post).

Choosing the “Excel” option from “Get Data”, a file dialog appears asking us to choose a file:

After clicking “Open”, the “Navigator” menu appears:

Notice that the worksheet appears, click on “Sheet1” to see the preview.

However, to bring the sheet into the dataset, the box next to the sheet in the menu must be checked.

Notice how the “Load” and “Edit” buttons are no longer greyed out. “Load” is highlighted in yellow as that is the default. Looking at the column headers, they have defaulted to numeric labelling because most of the first row of the Excel sheet is blank:

Click “Load” and let's see how our data looks if we import it as is. You'll notice on the right hand side, the "Fields" pane shows a Sheet1 table with the numerically labelled columns:

On the left sidebar of the Power BI screen you can see three icons:

• Report: where all your reports are and the space to create visualisations
• Data: displays the current table
• Relationships: displays the relationships between the tables in the dataset

Click on "Data" and our table will be displayed.

Note how the data is not ordered in the same way as it is in the file and the headers have moved. Normally in a database the order of the rows do not matter, but our table hasn't been loaded correctly.

Next time we'll be coming back to this dataset and going through the process of cleansing it in query editor.

See you next time for more Power BI Tips.

Errata

The following adjustment was made to the workbook provided by NASA:

Moved Sheet1!$I$394:$Q$394 to Sheet1!$J$394:$R$394 and Sheet1!$I$397:$Q$397 to Sheet1!$J$397:$R$397