As an accountant, you will know that organisations often have vast quantities of data stored in Excel. Sometimes, this data is in the form of lists and reports. When it comes to working with this data, Power BI is there to make things easier for us. The software allows us to connect to Excel data and carry out transformations which standardise the data into a usable format.
Power Query is the connection and transformation engine in Power BI, equipped with numerous connectors for different data sources, including Excel, SQL, Facebook and SAP.
When you connect to data in Power BI, Power Query Editor gives you the ability to transform the data into a usable format.
You can access the Power Query Editor by selecting Edit Queries in Power BI’s ‘Home’ ribbon.
Connecting to an Excel worksheet in Power BI
- Select Get Data
- Select Excel
- Choose a workbook
- A navigation screen will open - on the left-hand side, you will see the names of the worksheets available
- Tick the worksheets you want to load to the Query Editor, and select ok - you can load more than one worksheet at a time
- The Query Editor screen will then open - each worksheet selected will appear as a query on the left
Adjusting the data type
When data is imported, Power BI does its best to select the correct data type. However, it doesn’t always get it right. You should get into the practice of checking the data type of each column, just to make sure it’s correct. If you ever need to change the data type, follow these steps:
- Select the column that needs fixing
- Go to the Transform ribbon and select Data Type
- Choose your preference
- The data type for the column will update
Removing null fields
Sometimes, it’s necessary to remove null fields. This can be done using the Fill Up, or Fill Down transformation steps, also found in the Transform ribbon.
If you have a column that contains null in a number of fields. You can fill the column down by selecting ‘Fill Down’ from the Transform ribbon. By doing this, all of the null fields will be filled with the data from the cell above. It’s as easy as that!
Click here to view a video we've put together showing the steps above being put into action, which should help explain things further.
You need to sign in or register before you can add a contribution.