title
How to use Microsoft Power Query

description
In this step-by-step tutorial, learn how you can use Microsoft Power Query to get and clean up data in both Microsoft Excel and Microsoft Power BI. Power Query is the easiest way to connect, extract, transform and load data from a wide range of sources. 👋 Additional resources: - Sample files: https://1drv.ms/u/s!AmxrofZZlZ-whKZQTEiA4iLu-WPiaQ?e=AkAtOi - Download Power Query for Excel 2010 or 2013: https://www.microsoft.com/en-au/download/details.aspx?id=39379 ⌚ Timestamps 00:00 Introduction 00:59 Sample data 2:14 How to get Power Query 2:31 Get data 3:35 Combine data 5:26 Main Power Query interface 7:00 Transform data 11:21 Applied steps 12:06 Close & Load 12:33 Data imported into Excel 13:34 Insert pivot table & chart 14:46 Importing additional data 16:00 Wrap up 📃 Watch related playlists and videos - Pivot Table tutorial: https://youtu.be/m0wI61ahfLc - Excel: https://www.youtube.com/playlist?list=PLlKpQrBME6xLYoubjOqowzcCCd0ivQVLY - Power BI: https://www.youtube.com/playlist?list=PLlKpQrBME6xIAUqcPjFRPsMQJhgXdJVxe 🚩 Connect with me on social: - LinkedIn: https://www.linkedin.com/in/kevinstratvert/ - Twitter: https://twitter.com/kevstrat - Facebook: https://www.facebook.com/Kevin-Stratvert-101912218227818 🔔 Subscribe to my YouTube channel https://www.youtube.com/user/kevlers?sub_confirmation=1 🎬 Want to watch again? Navigate back to my YouTube channel quickly http://www.kevinstratvert.com 🛍 Support me with your Amazon purchases: https://amzn.to/3kCP2yz ⚖ As full disclosure, I use affiliate links above. Purchasing through these links gives me a small commission to support videos on this channel -- the price to you is the same.

detail
{'title': 'How to use Microsoft Power Query', 'heatmap': [], 'summary': 'Learn how to use microsoft power query for data analysis and transformation in excel and power bi, automating data retrieval and cleaning processes, reducing manual effort, and improving productivity, with a focus on 2017 data and streamlining data management for efficient analysis and visualization.', 'chapters': [{'end': 116.687, 'segs': [{'end': 30.568, 'src': 'embed', 'start': 0.069, 'weight': 0, 'content': [{'end': 1.51, 'text': 'Hi everyone, Kevin here.', 'start': 0.069, 'duration': 1.441}, {'end': 9.076, 'text': 'Today I want to show you how you can use Power Query that comes with Microsoft Excel and Power BI.', 'start': 1.69, 'duration': 7.386}, {'end': 15.801, 'text': 'First off, what is Power Query? Well, it helps you both get and also clean up your data.', 'start': 9.436, 'duration': 6.365}, {'end': 24.285, 'text': "What do I mean by that? Well, let's pretend that you're a business analyst and every month you're on point to analyze the monthly sales data.", 'start': 16.241, 'duration': 8.044}, {'end': 27.926, 'text': 'Now, one of the pains of the job is, well, first off, you have to get the data.', 'start': 24.665, 'duration': 3.261}, {'end': 30.568, 'text': 'So you go to some database or you find the latest Excel sheet.', 'start': 27.946, 'duration': 2.622}], 'summary': 'Power query in excel and power bi helps in getting and cleaning up data for analysis.', 'duration': 30.499, 'max_score': 0.069, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/0aeZX1l4JT4/pics/0aeZX1l4JT469.jpg'}, {'end': 109.361, 'src': 'embed', 'start': 80.948, 'weight': 1, 'content': [{'end': 84.95, 'text': "here's the excel sheet and it's a pretty standard order data sheet.", 'start': 80.948, 'duration': 4.002}, {'end': 90.712, 'text': 'i see order id, i see the customer, how many cookies were shipped and some additional information.', 'start': 84.95, 'duration': 5.762}, {'end': 93.213, 'text': 'now the pain is at the kevin cookie company.', 'start': 90.712, 'duration': 2.501}, {'end': 99.576, 'text': "they're paying me the big bucks to do analysis, but i end up spending most of my time just cleaning up data here.", 'start': 93.213, 'duration': 6.363}, {'end': 105.218, 'text': "for example, in column b, i don't know why they combine the customer id together with the customer name.", 'start': 99.576, 'duration': 5.642}, {'end': 109.361, 'text': 'Also, when I look at cookies shipped, they throw a dollar sign in front of this.', 'start': 105.718, 'duration': 3.643}], 'summary': 'Data analyst frustrated with data cleaning; customer info and cookie shipment data need fixing.', 'duration': 28.413, 'max_score': 80.948, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/0aeZX1l4JT4/pics/0aeZX1l4JT480948.jpg'}], 'start': 0.069, 'title': 'Using power query for data analysis', 'summary': 'Explains how power query in microsoft excel and power bi can automate data retrieval and cleaning processes, reducing the need for manual data manipulation. it provides an example of using power query to analyze order data at the kevin cookie company.', 'chapters': [{'end': 116.687, 'start': 0.069, 'title': 'Using power query for data analysis', 'summary': 'Explains how power query in microsoft excel and power bi can automate data retrieval and cleaning processes, reducing the need for manual data manipulation, and provides an example of using power query to analyze order data at the kevin cookie company.', 'duration': 116.618, 'highlights': ['Power Query automates the process of retrieving and cleaning data, reducing the need for manual manipulation, saving time and effort for business analysts analyzing monthly sales data.', 'The example demonstrates the need for data cleaning at the Kevin Cookie Company, such as separating customer ID and name, correcting the format of shipped cookies, and adding a profit column to the order data sheet.']}], 'duration': 116.618, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/0aeZX1l4JT4/pics/0aeZX1l4JT469.jpg', 'highlights': ['Power Query automates data retrieval and cleaning, saving time for analysts.', 'Example shows data cleaning needs at Kevin Cookie Company, like separating customer ID and name.']}, {'end': 317.332, 'segs': [{'end': 178.23, 'src': 'embed', 'start': 134.46, 'weight': 0, 'content': [{'end': 140.983, 'text': 'To access Power Query in the latest version of Excel, simply go up to the top ribbon and click on data.', 'start': 134.46, 'duration': 6.523}, {'end': 147.647, 'text': 'If you have Excel 2010 or Excel 2013, you need to install an add-in to be able to use Power Query.', 'start': 141.363, 'duration': 6.284}, {'end': 151.449, 'text': "I've included a link in the description where you can access that.", 'start': 148.107, 'duration': 3.342}, {'end': 157.954, 'text': 'Over on the left hand side, Power Query falls within this category, the get and transform data.', 'start': 151.949, 'duration': 6.005}, {'end': 164.219, 'text': 'If we click over on get data, we see all the different sources where we can pull data in from.', 'start': 158.354, 'duration': 5.865}, {'end': 166.961, 'text': "It's a vast collection of different places.", 'start': 164.619, 'duration': 2.342}, {'end': 170.904, 'text': "Basically, if you have data somewhere, you'll be able to pull it into Power Query.", 'start': 167.061, 'duration': 3.843}, {'end': 174.427, 'text': "When I hover over from file here, you'll see a few examples.", 'start': 171.324, 'duration': 3.103}, {'end': 178.23, 'text': 'One of the neat ones to call out is you can now pull in data from a PDF.', 'start': 174.527, 'duration': 3.703}], 'summary': 'Access power query in excel 2010/2013 with add-in. vast data sources available, including pdf.', 'duration': 43.77, 'max_score': 134.46, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/0aeZX1l4JT4/pics/0aeZX1l4JT4134460.jpg'}, {'end': 306.102, 'src': 'embed', 'start': 280.73, 'weight': 1, 'content': [{'end': 285.756, 'text': "but because I have multiple sheets and I want to combine them, I'll click on combine and transform data.", 'start': 280.73, 'duration': 5.026}, {'end': 290.778, 'text': 'This opens up another prompt where I can now combine all of my files.', 'start': 285.976, 'duration': 4.802}, {'end': 294.339, 'text': 'Up above, it allows me to choose a sample file.', 'start': 291.278, 'duration': 3.061}, {'end': 299.38, 'text': 'And by default, it simply selects the first file, which is the 2017 data.', 'start': 294.719, 'duration': 4.661}, {'end': 306.102, 'text': "I could also change it to any other year, but I think the 2017 data is fine, so I'll simply leave it set to the first file.", 'start': 299.72, 'duration': 6.382}], 'summary': 'Combining multiple sheets, selecting 2017 data for transformation.', 'duration': 25.372, 'max_score': 280.73, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/0aeZX1l4JT4/pics/0aeZX1l4JT4280730.jpg'}], 'start': 116.707, 'title': 'Leveraging power query and data transformation in excel and power bi', 'summary': 'Covers the efficient use of power query in excel for automating data integration, as well as data transformation tasks in microsoft excel and power bi, with a focus on 2017 data, reducing manual effort and improving productivity.', 'chapters': [{'end': 237.325, 'start': 116.707, 'title': 'Using power query in excel', 'summary': 'Highlights the efficient use of power query in excel to automate the process of combining data from multiple excel sheets, eliminating manual effort and ensuring a seamless data integration process, thus improving productivity.', 'duration': 120.618, 'highlights': ['Power Query allows users to easily pull in data from various sources, including files, PDFs, databases, Kusto, and Azure, streamlining the data integration process.', 'By utilizing Power Query, the user was able to effortlessly combine data from multiple Excel sheets, saving time and reducing manual effort.', 'The user was able to access Power Query within the latest version of Excel by clicking on the data tab, while users with Excel 2010 or 2013 can install an add-in to access Power Query.']}, {'end': 317.332, 'start': 237.706, 'title': 'Data transformation in microsoft excel and power bi', 'summary': 'Covers the process of transforming data to make modifications before loading it into microsoft excel or power bi, including tasks like removing dollar signs and splitting customer names. it also discusses the option to combine and transform multiple sheets and workbooks, with a focus on 2017 data.', 'duration': 79.626, 'highlights': ['The process of transforming data allows for modifications before loading it into Microsoft Excel or Power BI, such as removing dollar signs and splitting customer names. The ability to modify the data before pulling it into Microsoft Excel or Power BI, including tasks like getting rid of the dollar sign in front of a column and splitting out the customer name.', 'The option to combine and transform multiple sheets and workbooks is discussed, with a focus on the 2017 data. The process of combining all of the files, choosing a sample file (2017 data), and selecting the worksheet within the different workbooks, with a focus on the sheet called order data.']}], 'duration': 200.625, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/0aeZX1l4JT4/pics/0aeZX1l4JT4116707.jpg', 'highlights': ['Power Query streamlines data integration from various sources, including files, PDFs, databases, Kusto, and Azure.', 'Effortlessly combine data from multiple Excel sheets, saving time and reducing manual effort.', 'Access Power Query in the latest version of Excel by clicking on the data tab.']}, {'end': 788.932, 'segs': [{'end': 343.645, 'src': 'embed', 'start': 317.833, 'weight': 0, 'content': [{'end': 324.462, 'text': 'Over on the right-hand side here, I can see a preview of all of my data and it looks like it identifies it properly.', 'start': 317.833, 'duration': 6.629}, {'end': 326.065, 'text': "So now let's click on OK.", 'start': 324.843, 'duration': 1.222}, {'end': 329.129, 'text': 'This drops us in Power Query.', 'start': 327.006, 'duration': 2.123}, {'end': 337.198, 'text': "And if you've never used Power Query before and you're on point for analyzing data and getting data, you're going to learn to love this interface.", 'start': 329.309, 'duration': 7.889}, {'end': 343.645, 'text': "It might look a little bit intimidating at first, but I'm going to walk you through step by step how you can use it.", 'start': 337.678, 'duration': 5.967}], 'summary': 'Previewed data is identified properly, proceeding to use power query for data analysis.', 'duration': 25.812, 'max_score': 317.833, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/0aeZX1l4JT4/pics/0aeZX1l4JT4317833.jpg'}, {'end': 408.602, 'src': 'embed', 'start': 379.213, 'weight': 1, 'content': [{'end': 387.598, 'text': 'The way to think of this is this is kind of like running a macro and Power Query remembers every single transformation that we make on our data.', 'start': 379.213, 'duration': 8.385}, {'end': 393.304, 'text': 'Up on top, just like with most office applications, we see a ribbon with several different options.', 'start': 388.058, 'duration': 5.246}, {'end': 396.608, 'text': "We're going to use this to transform our data.", 'start': 393.645, 'duration': 2.963}, {'end': 400.753, 'text': 'In the center of the screen, I can see a sample of my data.', 'start': 397.008, 'duration': 3.745}, {'end': 408.602, 'text': "At the Kevin Cookie Company, we sell a lot of cookies, so it's only showing me the top 1000 rows of our order information.", 'start': 401.153, 'duration': 7.449}], 'summary': "Power query tracks data transformations like a macro, with a sample of 1000 rows from kevin cookie company's orders.", 'duration': 29.389, 'max_score': 379.213, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/0aeZX1l4JT4/pics/0aeZX1l4JT4379213.jpg'}, {'end': 792.416, 'src': 'embed', 'start': 766.068, 'weight': 2, 'content': [{'end': 771.432, 'text': "And the great thing is I didn't have to go through each individual sheet to make all of these changes.", 'start': 766.068, 'duration': 5.364}, {'end': 774.695, 'text': 'Here you see the customer ID is separate from the customer name.', 'start': 771.792, 'duration': 2.903}, {'end': 778.119, 'text': 'Here you see that all of them contain the days to ship.', 'start': 775.016, 'duration': 3.103}, {'end': 782.004, 'text': "So here it's simplified merging all of this data into one sheet.", 'start': 778.46, 'duration': 3.544}, {'end': 786.609, 'text': 'Over on the right hand side, I also have a pane for queries and connections.', 'start': 782.324, 'duration': 4.285}, {'end': 788.932, 'text': 'And here I see Power Query.', 'start': 787.029, 'duration': 1.903}, {'end': 792.416, 'text': 'If I right click on this, I can go down to show the peak.', 'start': 789.352, 'duration': 3.064}], 'summary': 'Data merged into one sheet, with separate customer id and name, days to ship included. power query utilized for efficient data handling.', 'duration': 26.348, 'max_score': 766.068, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/0aeZX1l4JT4/pics/0aeZX1l4JT4766068.jpg'}], 'start': 317.833, 'title': "Excel's power query and data transformation", 'summary': 'Provides an introduction to power query in excel, emphasizing its interface and functionality, and covers data transformation processes such as splitting columns, calculating profit, and merging sheets, aiming to simplify and automate data management.', 'chapters': [{'end': 466.917, 'start': 317.833, 'title': 'Introduction to power query in excel', 'summary': 'Introduces power query in excel, highlighting its interface, functionality, and the process of transforming and analyzing data step by step, aiming to facilitate data analysis for the user.', 'duration': 149.084, 'highlights': ['The Power Query interface provides a preview of the data and offers various options for transforming and analyzing it, ensuring proper identification of the data.', 'The applied steps in Power Query act like a macro, remembering every transformation made on the data, streamlining the process of data analysis and transformation.', "The process of transforming data in Power Query involves removing unnecessary columns and filtering out specific data entries, such as 'XXXXXX', to refine the dataset for analysis.", 'The interface includes a ribbon with different options for data transformation, providing a user-friendly experience for analyzing and transforming data.', 'The experience of using Power Query is initially intimidating, but the walkthrough aims to orient users and simplify the process of using the interface for data analysis.']}, {'end': 788.932, 'start': 467.337, 'title': 'Data transformation in excel', 'summary': 'Covers the process of transforming data in excel, including splitting columns, calculating profit, subtracting dates, removing and rearranging steps, and merging multiple sheets into one, resulting in a simplified and automated data management process.', 'duration': 321.595, 'highlights': ["Merged three years' worth of data into one sheet without manual intervention The process merged all three years of data into one sheet, eliminating the need for manual consolidation.", 'Calculated profit by subtracting the cost from the revenue The process involved calculating profit by subtracting the cost from the revenue, resulting in automated profit calculation for each entry.', 'Split customer ID and customer name into separate columns The chapter demonstrated the splitting of customer ID and customer name into separate columns, streamlining data organization and improving data clarity.', 'Subtracted ship date from order date to determine days to ship The process involved subtracting the ship date from the order date to determine the number of days it takes to ship, providing insights into the efficiency of the shipping process.', 'Automated steps for future data transformation through recorded macro-like steps The process recorded and automated transformation steps, enabling future automation of similar data transformations.']}], 'duration': 471.099, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/0aeZX1l4JT4/pics/0aeZX1l4JT4317833.jpg', 'highlights': ['The Power Query interface provides a preview of the data and offers various options for transforming and analyzing it, ensuring proper identification of the data.', 'The applied steps in Power Query act like a macro, remembering every transformation made on the data, streamlining the process of data analysis and transformation.', "Merged three years' worth of data into one sheet without manual intervention The process merged all three years of data into one sheet, eliminating the need for manual consolidation.", 'Automated steps for future data transformation through recorded macro-like steps The process recorded and automated transformation steps, enabling future automation of similar data transformations.', "The process of transforming data in Power Query involves removing unnecessary columns and filtering out specific data entries, such as 'XXXXXX', to refine the dataset for analysis."]}, {'end': 980.587, 'segs': [{'end': 816.809, 'src': 'embed', 'start': 789.352, 'weight': 0, 'content': [{'end': 792.416, 'text': 'If I right click on this, I can go down to show the peak.', 'start': 789.352, 'duration': 3.064}, {'end': 795.618, 'text': 'And here I can see a sample of the data once again.', 'start': 792.896, 'duration': 2.722}, {'end': 800.38, 'text': "But the thing that's really interesting is it's looking at this data source or this folder.", 'start': 795.718, 'duration': 4.662}, {'end': 809.445, 'text': "So if I bring additional files into this folder, it'll bring that data in automatically and it'll apply all of the transformations to that data.", 'start': 800.78, 'duration': 8.665}, {'end': 812.687, 'text': 'So I no longer have to get the data or clean it up.', 'start': 809.765, 'duration': 2.922}, {'end': 816.809, 'text': 'All of that happens for me automatically before I pull in additional data.', 'start': 812.907, 'duration': 3.902}], 'summary': 'Automatically applies transformations to new data, saving time and effort.', 'duration': 27.457, 'max_score': 789.352, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/0aeZX1l4JT4/pics/0aeZX1l4JT4789352.jpg'}, {'end': 858.293, 'src': 'embed', 'start': 830.156, 'weight': 2, 'content': [{'end': 832.457, 'text': "I'll stick with all the defaults and click on OK.", 'start': 830.156, 'duration': 2.301}, {'end': 834.378, 'text': 'This opens up a pivot table.', 'start': 832.698, 'duration': 1.68}, {'end': 838.961, 'text': "If you've never worked with pivot tables before, they make analysis so much easier.", 'start': 834.499, 'duration': 4.462}, {'end': 844.424, 'text': "I've included a link in the description that'll get you up to speed on how you can use pivot tables.", 'start': 839.381, 'duration': 5.043}, {'end': 849.948, 'text': 'I want to track our profit by year and once again, pivot tables make that so easy.', 'start': 844.944, 'duration': 5.004}, {'end': 853.75, 'text': "I could simply click on profit and I'll drag it down to values.", 'start': 850.348, 'duration': 3.402}, {'end': 858.293, 'text': "Next, I want to track the profit by the order date so I'll drag that into rows.", 'start': 854.271, 'duration': 4.022}], 'summary': 'Using pivot tables for profit analysis by year and order date.', 'duration': 28.137, 'max_score': 830.156, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/0aeZX1l4JT4/pics/0aeZX1l4JT4830156.jpg'}, {'end': 959.794, 'src': 'embed', 'start': 937.694, 'weight': 1, 'content': [{'end': 946.782, 'text': 'Number two, if I go back to the original sheet with all of my data, if I click on the order date here, you see now that 2020 data has been included.', 'start': 937.694, 'duration': 9.088}, {'end': 953.388, 'text': 'So Power Query not only gets my data, but it makes all of the same changes and transformations to that data.', 'start': 947.183, 'duration': 6.205}, {'end': 959.794, 'text': 'Now I could spend my time on more valuable things like analyzing this data and trying to make sense of it.', 'start': 953.869, 'duration': 5.925}], 'summary': 'Power query includes 2020 data, saves time for analysis.', 'duration': 22.1, 'max_score': 937.694, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/0aeZX1l4JT4/pics/0aeZX1l4JT4937694.jpg'}], 'start': 789.352, 'title': 'Automated data analysis and visualization and streamlining data management with power query', 'summary': 'Discusses automated import and transformation of data, pivot table and chart creation for profit analysis, and visualization. additionally, it demonstrates how power query streamlines data management, saving time and enabling deeper analysis for efficient data cleaning and analysis.', 'chapters': [{'end': 897.825, 'start': 789.352, 'title': 'Automated data analysis and visualization', 'summary': 'Discusses how to automatically import and transform data, create pivot tables and pivot charts to analyze profit by year and order date, and visualize profit using pivot charts, making data analysis and visualization easier.', 'duration': 108.473, 'highlights': ['The data source or folder automatically brings in additional files and applies all transformations to the data, eliminating the need to manually get or clean up the data.', 'Pivot tables make analysis easier by facilitating tracking of profit by year and order date, simplifying data visualization and analysis.', 'The process demonstrates the automatic import and transformation of 2020 data, showcasing the efficiency and effectiveness of the data handling approach.']}, {'end': 980.587, 'start': 897.825, 'title': 'Streamlining data management with power query', 'summary': 'Demonstrates how power query streamlines data management by automatically applying changes and transformations to new data, saving time and enabling deeper analysis, making it an efficient tool for data cleaning and analysis.', 'duration': 82.762, 'highlights': ['Power Query streamlines data management by automatically applying changes and transformations to new data, saving time and enabling deeper analysis.', 'By using Power Query, the speaker avoids the need to manually apply changes and transformations to the 2020 file, showcasing the efficiency of the tool.', 'When refreshing the data, Power Query automatically pulls in the 2020 data, demonstrating its ability to efficiently handle new datasets.', 'Power Query not only retrieves data but also applies the same changes and transformations to the new data, freeing up time for deeper analysis.']}], 'duration': 191.235, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/0aeZX1l4JT4/pics/0aeZX1l4JT4789352.jpg', 'highlights': ['The data source or folder automatically brings in additional files and applies all transformations to the data, eliminating the need to manually get or clean up the data.', 'Power Query streamlines data management by automatically applying changes and transformations to new data, saving time and enabling deeper analysis.', 'Pivot tables make analysis easier by facilitating tracking of profit by year and order date, simplifying data visualization and analysis.']}], 'highlights': ['Power Query automates data retrieval and cleaning, saving time for analysts.', 'Power Query streamlines data integration from various sources, including files, PDFs, databases, Kusto, and Azure.', 'The Power Query interface provides a preview of the data and offers various options for transforming and analyzing it, ensuring proper identification of the data.', "Merged three years' worth of data into one sheet without manual intervention The process merged all three years of data into one sheet, eliminating the need for manual consolidation.", "The process of transforming data in Power Query involves removing unnecessary columns and filtering out specific data entries, such as 'XXXXXX', to refine the dataset for analysis.", 'The data source or folder automatically brings in additional files and applies all transformations to the data, eliminating the need to manually get or clean up the data.', 'Pivot tables make analysis easier by facilitating tracking of profit by year and order date, simplifying data visualization and analysis.', 'Effortlessly combine data from multiple Excel sheets, saving time and reducing manual effort.', 'Automated steps for future data transformation through recorded macro-like steps The process recorded and automated transformation steps, enabling future automation of similar data transformations.', 'Example shows data cleaning needs at Kevin Cookie Company, like separating customer ID and name.', 'Access Power Query in the latest version of Excel by clicking on the data tab.', 'The applied steps in Power Query act like a macro, remembering every transformation made on the data, streamlining the process of data analysis and transformation.', 'Power Query streamlines data management by automatically applying changes and transformations to new data, saving time and enabling deeper analysis.']}