Subscriber only lesson.
Sign up to this course to view this lesson.
About this lesson
In this module we will explore collecting Budget data to link into our dashboards. The only issue is that it comes from a completley different source: an Excel file.
Adding Excel Data to an Existing Power BI Model
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.
There are a great 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
- 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 future
Lesson notes are only available for subscribers.