Collecting More Data

Collecting More Data

Working with Excel data in Power BI

When to use

When you need to add new data into your Power BI solution that lives in an Excel file

Instructions

There are many different connectors that you can get to via the Get Data button.  As there are too many to focus on, we will look at some useful techniques for cleaning data here

Generate headers if required:

• Databases and tables will have this done already, other sources may not
• Use the Remove Top Rows command to get rid of unnecessary header rows
• Promote a row to headers
• Rename columns that don’t make sense
• Filter out any data that you can

Filling into gaps:

• Use the Fill Up or Fill Down commands to fill data into areas that contain null values
• If you need to fill across rows, go to Transform -> Transpose first, then fill, then Transpose the data back

UnPivoting Data:

• Select the column(s) that you wish to keep
• Right-click one of the selected column headers à UnPivot Other Columns
• Review the attribute column and filter out anything that shouldn’t be there
• Rename the Attribute and Value columns appropriately

Finalize the query:

• Set your final data types
• Rename your query

Hints & tips

• If the column headers are dates, DO NOT SET THE DATA TYPES BEFORE UNPIVOTING. Doing so hard codes the dates into the code, meaning that your query will break in the future.
• When filtering data out of a pivoted attribute column remember that you can often trigger errors by setting data types, then use the remove errors command to get rid of those rows.