Power Query is an incredibly powerful tool built into programs like Excel and Power BI that lets you import, clean, transform, and shape data from various sources. This cleaned data can then be made into visualisations or tables.
How to access Power Query from Power BI:
After Clicking load data and selecting the file you want to import, you will be met with the Navigator.
When a sheet is selected, an option to load or transform data becomes available in the bottom right of the navigator.
Clicking load will bring the data straight into power bi to be used in making charts and visualisations. Clicking transform, on the other hand will take you into Power Query where the data can be cleaned and transformed prior to visualisation.
The image above shows the layout of Power Query. There are the different tabs across the top, in which the different functions to transform and clean your data are.
Using this hospitality dataset, this blog is going to run through a few simple ways that Power Query can help to clean and transform your data.
The first thing i have noticed is that adult child mix has been picked up as a date.
Click on the field type at the top of the column and change it to text.
We can also combine the check-in Day, Month and Year to make a check-in date. This is done by merging the columns. Selecting all three columns you want to merge and then clicking the merge columns button under the transform tab will merge all three columns into one.
The next step is changing the format type from text to date.
Another useful function in Power Query is the split function. This can be useful for when trying to isolate one specific part of a string.
In this dataset we have a field called "reservation_id" formatted as follows:
Using the split function, we can split this reservation ID into 4 different fields, each representing the values between the delimiters (in this case "-").
This is done by selecting this field and clicking on split column by delimiter in the transform section.
Splitting by each occurrence of the selected delimiter will create multiple fields according to the values between the delimiters.
Hopefully after reading this introduction to Power Query, you have an idea of some of the basic capabilities of the software.