How to Use the Query Function to Clean and Combine Data for Power BI

Hi everyone! In this blog post, I want to share with you how I transformed data to import to Power BI using the query function. This is a very useful skill for anyone who wants to work with data and create amazing visualizations.

I used three csv files from three years of sales of an international company. These files had different formats and structures, so I had to clean and transform them before importing them to Power BI.

Some of the topics that I learned are:

  • Renaming properties: This helps to make the column names more descriptive and consistent.
  • Choosing columns: This allows me to select only the columns that I need for my analysis and remove the rest.
  • Choosing lines: This lets me pick only the rows that are relevant for my data set and exclude the others.
  • Filtering columns and lines: This enables me to apply criteria to filter out unwanted values or outliers from my data.
  • Observing column health: This shows me how many errors, empty values or duplicates are in each column and how to fix them.
  • Observing quality of the column: This indicates how well the data in each column matches the expected data type and format.
  • Observing distribution of the column: This displays how the data in each column is distributed and helps me identify patterns or trends.
  • Adding and mixing new columns: This gives me the ability to create new columns from existing ones using formulas or functions.
  • Substituting data from a cell: This allows me to replace a specific value in a cell with another value of my choice.
  • Verifying types of data imported: This ensures that the data types of each column are correct and compatible with Power BI.

At the end, I was able to maintain my three original year sales tables and create a new table with all the three years filtered, fixed, and combined together. This made it easier for me to import them to Power BI and create some awesome visualizations and insights.

Overall, I learned the basics of the process of importing data and making sure that these data will be sent to Power BI in the best possible way to create new visualizations and help me in my story telling.

I am excited to apply this knowledge in a project and I hope you found this blog post helpful. Thanks for reading!

Leave a Comment

Your email address will not be published. Required fields are marked *