Go Summarize

Convert Your Query into an M Function | Combine Data from Multiple Excel Sheets in Power BI

How to Power BI2021-04-06
power query#m power query#power query m#combine data power query#combine data m#combine power bi#power bi combine#power bi m#m language power bi#power bi m language#convert with m#m in power bi#learn m power bi#power bi learn m#power bi#power bi tutorials for beginners#power bi desktop#convert query#m power bi#m powerbi#powerbi m#m language#custom function in power bi#custom function power bi
13K views|3 years ago
💫 Short Summary

In this Power BI tutorial, you will learn how to combine data from different sheets in an Excel workbook using Power Query. The process involves connecting to the workbook, transforming the data, removing unnecessary steps, duplicating the query, updating the source tab, combining the queries using the append transformation, and finally using custom functions for a more elegant solution. Custom functions allow for the reuse of queries and the application of transformations to new data, making the data analysis process more efficient and scalable.

✨ Highlights
📊 Transcript
Introduction to combining data from different sheets in an Excel workbook using Power Query.
00:00
Connect to the Excel workbook that contains the data sets to be combined.
Transform the data from the first sheet (e.g., 2019) by removing unnecessary rows, promoting headers, filling down values, renaming columns, and filtering and unpivoting data.
Turning the query into a function to reuse for other sheets.
02:55
Rename the query for the second sheet (e.g., 2020) and remove unnecessary steps.
Remove the reference to the previous step and replace it with a variable as an input parameter.
Create a function by adding the parameter name and its value.
Filter out the sheets that are not needed for data combination.
Invoke the function to apply transformations to the nested tables.
Combining and transforming the data sets from different years.
06:47
Remove unnecessary columns and keep the transformed data and year name columns.
Expand the data sets and combine them.
Merge the month and year columns to create a new 'date' column.
Apply data types to the columns.
💫 FAQs about This YouTube Video

1. What are the options for combining new data from different sheets in Excel without having to redo the process every time?

The options for combining new data from different sheets in Excel without having to redo the process every time are: copying over the data from one sheet to another, duplicating the query and updating the source tab, and using custom functions for a more elegant solution.

2. How can custom functions be used to solve the issue of combining data from different sheets in Excel?

Custom functions can be used to solve the issue of combining data from different sheets in Excel by creating a function that can be reused for other sheets, applying transformations to the data, and having a table as an output.

3. What is the process for combining data from different sheets in an Excel workbook using Power Query?

The process for combining data from different sheets in an Excel workbook using Power Query involves connecting to the workbook, transforming the data, removing unnecessary steps, duplicating the query, updating the source tab, combining the queries using the append transformation, and finally using custom functions for a more elegant solution.