Power BI

Power Query to Power BI: How to Clean & Analyze Data in Power BI Dashboards

by

Power Query to Power BI: Load, Clean & Analyze Multi Level Data in Power BI Quickly & Easily

Learn the hidden Tips, Tricks & Techniques to Load and Clean Dirty Data within a few Clicks in Power Query (in Power BI), and then Analyze the data in useful visualization charts, tables, maps in a pretty dashboard for the management.

Slice the data by the year, Country, Category or a combination of these easily.

This Power Query & Power BI tutorial video is recorded by Vinai Prakash, creator or @ExcelChamp and founder of ExcelChamp.Net site.

Subscribe to ExcelChamp YouTube Channel.

This Step by Step Tutorial on How to Clean the Dirty Data with multiple levels of information, but without any headings, and then how to create data visualizations in Power BI Dashboards.

We will show you how to:

  1. How to load an Excel sheet in Power BI
  2. How to Clean the Data in Power Query (within Power BI)
  3. How to Undo a few automatically done steps within Power Query
  4. Transpose a whole table in Power Query
  5. How to fill missing values from the values provided from the rows above.
  6. How to promote the First Row Headers to be the Column Headers in Power Query
  7. How to flip the columns into rows by using the Unpivot Other Columns command in Power Query
  8. How to Change the Column Headings to be more appropriate
  9. How to rename a Query within Power Query
  10. How to change the data type of any column of data in Power Query
  11. How to Close and Apply in Power Query to finish the loading of data to Power BI
  12. How to create simple Table visualizations in Power BI
  13. How to add a Pie Chart in Power BI Visualizations
  14. How to Style and Format a Table values, and how to format numeric values in Power BI
  15. How to Add a Map Visualization in Power BI
  16. How to Add a Slicer in Power BI Dashboard
  17. How to Use values in any Visualization to Slice and Cross Filter other Visualizations and charts.
  18. How to add a nice heading to your Power BI Dashboard
  19. How to change the Page name in Power Bi
  20. How to Analyze the Completed Dashboard in Power BI
  21. What happens when new data arrives. How to add new data to the Excel Data file
  22. How to get the latest data to appear in the Power BI Dashboard in a single click
  23. Beginning of full data analysis.

This mini, step by step Power Query and Power BI Tutorial by @ExcelChamp Vinai Prakash has been created to demonstrate the powerful features built within Power Query and Power BI Data Visualizations.

For full, more detailed training on Power BI Data Analytics and Data Visualization, contact us.

What is Power Query in Power BI?

Power Query is a data transformation and data preparation tool available in Microsoft Power BI, Excel, and other Microsoft products. It is a powerful and flexible tool that allows users to connect, combine, and refine data from various sources to create clean, structured, and analytically useful data sets.

Here are some key features and functionalities of Power Query in Power BI:

Data Source Connectivity: Power Query supports a wide range of data sources, including databases, Excel files, SharePoint lists, web pages, OData feeds, JSON, XML, and many others. It simplifies the process of connecting to these diverse data sources and importing data.

Data Transformation: With Power Query, you can perform various data transformation operations like filtering, sorting, grouping, pivoting, unpivoting, merging, appending, and more. It enables you to clean, reshape, and enrich your data before loading it into Power BI for analysis.

Query Editor: Power Query provides a user-friendly Query Editor interface where you can visually build data transformation steps. You can see a preview of your data at each transformation step and easily modify the applied steps to achieve the desired data structure.

Applied Steps: Each data transformation operation in Power Query is recorded as an “Applied Step.” This means that all the data cleaning and manipulation actions you perform in the Query Editor are stored sequentially in the Query Settings, and you can revisit and modify any step at any time.

M Language: Power Query uses the M language behind the scenes to define the data transformation steps. While most users can leverage the Query Editor’s visual interface without writing any code, advanced users can also dive into the M code to perform custom transformations and create more complex queries.

Data Load: After you’ve applied all the necessary data transformations, you can load the cleaned data into Power BI’s data model for further analysis and visualization.

By utilizing Power Query in Power BI, you can significantly reduce the time and effort required to prepare data for analysis, ensuring that you work with clean and reliable datasets that are ready for insightful reporting and visualization.

 

Why Should You Learn Data Visualization With Power BI?

Learning Power Query, DAX Formulas, and Power BI data visualization offers several compelling reasons for individuals and organizations:

Data Transformation and Cleaning: Power Query allows you to import data from various sources and perform data transformation and cleaning tasks efficiently. It helps streamline the data preparation process, ensuring data accuracy and consistency.

Advanced Data Modeling: DAX (Data Analysis Expressions) enables you to create powerful data models and perform complex calculations on the data. It allows for creating calculated columns, measures, and calculated tables, providing deeper insights into the data.

Seamless Data Integration: Power BI integrates seamlessly with numerous data sources, databases, and online services. Learning Power Query and DAX makes it easier to bring in data from diverse sources and create a unified view for analysis.

Enhanced Data Visualization: Power BI’s data visualization capabilities are robust and intuitive. Learning data visualization techniques helps you represent data in compelling ways, making it easier to derive insights and communicate findings effectively.

Interactive Dashboards: Power BI enables the creation of interactive and dynamic dashboards. By mastering these tools, you can build engaging dashboards that allow users to explore data and gain actionable insights in real-time.

Informed Decision-Making: With Power Query, DAX, and Power BI data visualization skills, you can transform raw data into meaningful information. This empowers decision-makers to make data-driven decisions, leading to improved business outcomes.

Career Opportunities: Power Query, DAX, and Power BI are widely used in industries and organizations for data analysis and reporting. Acquiring these skills enhances your employability and opens up career opportunities in data analytics and business intelligence roles.

Time and Cost Efficiency: Learning these tools can lead to more efficient data analysis and reporting processes. Automated data transformation, advanced calculations, and interactive visualizations save time and resources, increasing overall productivity.

Scalability and Flexibility: Power BI is a scalable tool that can handle large datasets and complex data models. Learning its features allows you to work with diverse data scenarios and adapt to different business needs.

Stay Ahead in the Industry: In today’s data-driven world, being proficient in Power Query, DAX, and Power BI data visualization gives you a competitive edge. It positions you as a valuable asset in any organization looking to leverage data for growth and success.

Learning Power Query, DAX Formulas, and Power BI data visualization is a strategic investment in your professional development, providing the skills and tools needed to excel in the realm of data analytics and business intelligence.

Invest in your future.

Cheers,
Vinai Prakash
Founder & Principal Trainer of ExcelChamp.Net

Power Query: The Hidden Feature of Excel To Clean Dirty Data in Minutes

by

Learn how Power Query, a tool embedded in your current version of Excel can help you to clean dirty data that is made up of multiple levels, but without any headings or format.

It only takes a few clicks to clean this data and then we can analyze it using a Excel Pivot Table.

A Quick and simple step by step tutorial on using Power Query in Excel and then Pivot Tables to Analyze the data, and Slice it using Slicers.

How To Clean Dirty Data With Microsoft Power Query within Excel & Power BI

Presented by Vinai Prakash, Founder of ExcelChamp.Net

Practice Excel File for Your Benefit 

This video demonstrates Power Query in Microsoft Excel 365, Excel  2019, Excel 2021, Excel 2016, Excel 2013, Excel 2010.

The key data cleanup tools within Power Query that are demonstrated are:

  •  How to Transpose the data in Power Query
  •  How to Change the Name of a Query in Power Query
  •  How the steps of the cleanup are recorded within Power Query
  •  How to Rename Columns in Power Query
  •  How to Promote the First Row as Headers in Power Query
  •  How to Fill Down Values from the rows above in Power Query
  •  How to Unpivot Other Columns in Power Query
  •  How to Save & Load the Cleaned up Data in Power Query
  •  How to Create a Pivot Table in Microsoft Excel
  •  How to Filter Data in a Pivot Table
  •  Limitations of the Filter Section in an Excel Pivot Table
  •  How to Add a Slicer in Excel Pivot Tables
  •  How to select multiple values in a Slicer, and
  •  How the Slicer is superior than a Filter in Excel

The Power Query features shown in this video work exactly the same way in Microsoft Power BI also. So you can use this video steps for Power Query within Power BI.

Do like the video, subscribe to our channel, and Give our video a Thumbs Up.

We’d love a comment about how this video helped you. I reply to all comments personally. Thanks!

What is Power Query?

Power Query is a data transformation and data preparation tool available in Microsoft Excel. It is designed to help users import, transform, and combine data from various sources for analysis and reporting purposes. Power Query provides a user-friendly interface and a wide range of data manipulation capabilities, making it easier to clean, reshape, and combine data from multiple sources.

Here are some key features and functionalities of Power Query in Excel:

  1. Data Import: Power Query allows you to import data from various sources, including databases, Excel files, text files, CSV files, web pages, SharePoint lists, and more. It provides a seamless way to connect to and retrieve data from external sources.
  2. Data Transformation: Power Query provides a set of transformation options to clean and reshape data. You can perform tasks such as removing duplicates, filtering rows, splitting columns, merging tables, changing data types, applying calculations, and performing advanced transformations using a visual interface or custom functions.
  3. Data Connection and Refresh: Power Query enables you to create connections to data sources and set up automatic data refresh. This ensures that your Excel workbook stays up-to-date with the latest data from the connected sources, saving you time and effort in manual data updates.
  4. Data Cleanup and Data Quality: Power Query offers various data cleansing and data quality features. You can perform operations like removing empty rows, handling null values, replacing values, standardizing data formats, and detecting and correcting data inconsistencies.
  5. Query Folding and Performance Optimization: Power Query optimizes query performance by utilizing query folding whenever possible. Query folding pushes data transformations to the underlying data source, resulting in faster and more efficient data retrieval and processing.
  6. Data Combining and Merging: With Power Query, you can easily combine and merge data from multiple sources. It allows you to merge tables based on common columns, append data from multiple tables, and perform complex join operations to create a unified dataset.
  7. Custom Data Transformations: Power Query provides advanced capabilities to create custom data transformations using the M formula language. You can write custom functions, perform complex calculations, and create reusable query components to streamline your data transformation workflows.
  8. Data Visualization and Reporting: Once the data is imported and transformed using Power Query, you can load it into Excel for further analysis or create reports using Excel’s data visualization tools, such as PivotTables, PivotCharts, and Power View.

Power Query is available as a built-in feature in Excel 2016 and later versions. It offers a powerful and intuitive way to clean, shape, and combine data from various sources, empowering users to perform advanced data analysis and reporting tasks with ease.

Who Should Learn Power Query?

Power Query is a powerful data transformation and preparation tool in Excel that can benefit a wide range of individuals and professionals.

Here are some key groups of people who should consider learning Power Query:

  1. Data Analysts and Business Analysts: Data analysts and business analysts who work extensively with data can greatly benefit from learning Power Query. It enables them to import and transform data from multiple sources, clean and reshape data, and create consolidated datasets for analysis and reporting purposes.
  2. Excel Power Users: Individuals who are already proficient in Excel and frequently work with data can enhance their skills by learning Power Query. It provides advanced data manipulation capabilities beyond Excel’s built-in functions and features, allowing for more efficient and flexible data preparation.
  3. Financial Professionals: Professionals in finance and accounting roles can leverage Power Query to streamline data analysis and reporting tasks. It enables them to import and combine financial data from various sources, perform data cleansing and transformations, and generate accurate and customized financial reports.
  4. Data Scientists and Researchers: Data scientists, researchers, and individuals working in data-intensive fields can use Power Query to preprocess and prepare data for analysis. It enables them to import and combine large datasets, handle data cleaning and transformation tasks, and create structured datasets ready for advanced analysis.
  5. IT Professionals and Data Engineers: IT professionals and data engineers who are involved in data integration and management can benefit from Power Query. It allows them to connect to various data sources, transform and cleanse data, and prepare it for further processing or loading into data warehouses or data lakes.
  6. Project Managers: Project managers who work with data and need to consolidate and analyze project-related information can use Power Query to streamline their data preparation workflows. It enables them to combine data from different project sources, clean and reshape project data, and create consolidated reports or dashboards.
  7. Sales and Marketing Professionals: Sales and marketing professionals can utilize Power Query to extract and transform data from CRM systems, web analytics tools, and other marketing platforms. It enables them to analyze and consolidate sales and marketing data, create custom reports, and derive insights to optimize sales and marketing strategies.
  8. Administrators and Operations Professionals: Professionals responsible for managing administrative tasks, operations, and data-driven processes can benefit from learning Power Query. It allows them to import and transform data from various systems, automate data cleaning and preparation, and streamline operational workflows.
  9. Educators and Trainers: Educators and trainers who teach data analysis or Excel-related topics can enhance their curriculum by including Power Query. Teaching Power Query equips students with valuable skills in data preparation and manipulation, enhancing their data analysis capabilities.
  10. Any Excel User Seeking Advanced Data Preparation Skills: Even individuals who use Excel for personal or basic business purposes can benefit from learning Power Query. It provides them with advanced data manipulation capabilities, enabling them to clean, transform, and combine data effectively.

Learning Power Query can benefit individuals in various roles and industries, empowering them to efficiently handle data preparation tasks and derive valuable insights from their data.

Is Power Query Easy To Learn?

Power Query is generally considered to be user-friendly and relatively easy to learn, especially for individuals who are already familiar with Excel. Here are a few factors that contribute to the ease of learning Power Query:

  1. Visual Interface: Power Query provides a visual interface within Excel, allowing users to perform data transformations and manipulations through a series of intuitive steps. The interface provides a clear representation of the applied transformations, making it easier to understand and modify the data preparation process.
  2. Intuitive Operations: Power Query offers a wide range of built-in transformations and operations that can be applied to data. These include filtering, sorting, splitting columns, merging tables, pivoting, and much more. The operations are designed to be user-friendly and logical, making it easier for users to select and apply the required transformations.
  3. Query Dependencies and Applied Steps: Power Query keeps track of the applied transformations and presents them as a series of applied steps. Each step is displayed in a structured manner, allowing users to review, modify, or remove any step in the data preparation process. The ability to view and edit applied steps provides transparency and makes it easier to troubleshoot and adjust the transformations.
  4. Query Editor Preview: Power Query provides a preview feature that allows users to see the results of applied transformations in real-time. This live preview helps users understand the impact of each step and make adjustments as needed, ensuring the desired data output.
  5. Reusability and Automation: Power Query supports the creation of reusable queries, which can be applied to multiple datasets. Users can define a set of transformations once and then reuse them on different data sources, saving time and effort. Power Query also allows for automation by enabling users to set up scheduled data refreshes or create connections to external data sources.

While Power Query is generally considered user-friendly, the complexity of data transformation tasks can vary. Advanced data transformations or complex data structures may require more in-depth understanding and practice.

With the availability of classroom trainings, documentation, and community support, users can find ample resources to assist them in learning and mastering Power Query.

Intellisoft Training runs a Power BI MasterClass, which covers quite a bit of cleanup of data using Power Query functions and features.

Feel free to join it, or engage Intellisoft for a Corporate Training on Data Analytics & Data Visualization With Power BI

Overall, users with basic Excel skills and a willingness to explore and experiment can quickly grasp the concepts and functionalities of Power Query  in Power BI and start utilizing it for data preparation and analysis tasks.

I hope you enjoyed this article, and the Power Query video. Do write a comment, and let me know what you liked about it.

Cheers,
Vinai Prakash
Founder of ExcelChamp.Net

 

Power BI Creates Left Joins By Default

by

Microsoft Power BI is a great tool to visualize data quickly and create management dashboards.
We can load data from almost any data source, and create relationships between different tables, just like in a traditional RDBMS (Relational Database).

Join appear as Equal Joins in Power BI.

This means that when we join 2 or more tables, with one table being the dimension table, and the other the Fact table, then the dimension filters the fact table records that match the criteria (which can come from a Dimension Table, Page filter, Report filter, or through the interactions between the different visualizations)

So a Matrix report or a Chart in Power BI will only show values from the Transaction table where the row from the dimension matches.

  • If we want to see all categories from the Product table, we can just select a category.
  • This will display all distinct categories, irrespective of whether there were any sales for these product categories or not.
  • However, as soon as we bring in the Sales Amount from the Sales Fact table, it filters down the sales amount for each category and then summarizes it.
  • This means that only the categories where there were sales will be displayed.
  • And the categories where there are no sales will be ignored and hidden.

But by tweaking the Category to display Values With No Data, we can see all categories, with or without corresponding sales.

See the step by step video to learn how to enable the Show Values With No Data option in Power BI.

I hope you like the tip. Do like it, and subscribe to the ExcelChamp’s YouTube Channel for more PowerBI Tips like this.

Thanks,
Vinai