title
Power Query Tutorial - What is it, how to use it & 4 complete examples + Free download

description
Power Query (Get & Transform data in Excel) is a true game changer 💥. It can simplify and automate various data activities. In this in-depth video tutorial, learn all about this powerful time-saving technology. You will learn: * What is Power Query and how to launch it from Excel or Power BI? * How to connect and load data from any type of sources - excel files, CSVs, text files, web pages, PDF, folders etc. * How to perform common data cleansing activities with Power Query * Adding columns using rules * Automating data collection, clean-up and publishing processes with Power Query * Web scraping with Power Query * SQL style Joins, merges, appends and unions with Power Query * Consolidating data in a folder * Data load, save & publish process * Tips & tricks on using Power Query efficiently For sample datasets, completed workbook and mind-map PDF, visit: ======================================================= https://chandoo.org/wp/power-query-tutorial/ Timestamps: 0:00 - Introduction to Power Query 3:34 - Power Query as a mind-map 24:10 - Example 1 - Employee data load, clean-up and analysis 47:09 - Example 2 - Web scraping with Power Query 58:48 - Example 3 - Consolidating a folder of files with Power Query 1:09:08 - Example 4 - Merging, joining and appending datasets with Power Query 1:22:43 - Closing remarks - how to learn more, books & website recommendations Recommended book: Combine, collect & transform data with Power Query by Gil Raviv - https://amzn.to/2Nqiae8 Recommended course: Excel School program - data cleansing, analysis and visualization course by Chandoo - https://chandoo.org/wp/excel-school-program/ #PowerQuery #Excel #PowerBI

detail
{'title': 'Power Query Tutorial - What is it, how to use it & 4 complete examples + Free download', 'heatmap': [{'end': 2608.012, 'start': 2494.493, 'weight': 0.706}], 'summary': "Tutorial provides a comprehensive overview of power query, emphasizing its features, examples, and applications, including data transformation, web scraping, consolidation, merging, and appending tables, with access to sample datasets for practice, showcasing the tool's dynamic capabilities and practical usage.", 'chapters': [{'end': 208.308, 'segs': [{'end': 61.676, 'src': 'embed', 'start': 30.189, 'weight': 0, 'content': [{'end': 39.652, 'text': 'transformation and data maintenance jobs that tend to take up quite a bit of our time as analysts or reporting professionals.', 'start': 30.189, 'duration': 9.463}, {'end': 48.416, 'text': 'So I believe that just by learning Power Query and using it efficiently, you can save a ton of time.', 'start': 40.273, 'duration': 8.143}, {'end': 54.594, 'text': 'And the best part of Power Query is it is actually a transferable skill.', 'start': 49.753, 'duration': 4.841}, {'end': 61.676, 'text': 'So if you know how to use Power Query, you can use it both in Excel and also in Power BI.', 'start': 54.974, 'duration': 6.702}], 'summary': "Learning power query can save analysts time, as it's transferable to excel and power bi.", 'duration': 31.487, 'max_score': 30.189, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PiFAa_jjaEI/pics/PiFAa_jjaEI30189.jpg'}, {'end': 162.33, 'src': 'embed', 'start': 115.487, 'weight': 2, 'content': [{'end': 118.429, 'text': 'how to clean it up and how to publish and analyze that.', 'start': 115.487, 'duration': 2.942}, {'end': 119.651, 'text': "That's the first example.", 'start': 118.55, 'duration': 1.101}, {'end': 125.171, 'text': 'The second example is how to do web scraping through Power Query.', 'start': 120.566, 'duration': 4.605}, {'end': 134.742, 'text': 'So you can use Power Query to connect to any kind of websites and internal internet websites like SharePoint or other types of portals,', 'start': 125.552, 'duration': 9.19}, {'end': 140.369, 'text': 'scrape the data in a structured manner and maintain the data in Excel or Power BI for analysis.', 'start': 134.742, 'duration': 5.627}, {'end': 144.533, 'text': 'So how to do that? The third example is consolidation.', 'start': 140.789, 'duration': 3.744}, {'end': 151.72, 'text': "Let's just say you got a folder full of invoices or purchase receipts or project files or something else,", 'start': 145.033, 'duration': 6.687}, {'end': 154.822, 'text': 'and you want to combine all of that into one neat table.', 'start': 151.72, 'duration': 3.102}, {'end': 157.265, 'text': "How to do that? So that's the third example.", 'start': 154.883, 'duration': 2.382}, {'end': 162.33, 'text': 'And the fourth example is how to do merges and append tables.', 'start': 157.645, 'duration': 4.685}], 'summary': 'Learn to clean, publish, and analyze data, perform web scraping using power query, consolidate data, and merge/append tables.', 'duration': 46.843, 'max_score': 115.487, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PiFAa_jjaEI/pics/PiFAa_jjaEI115487.jpg'}, {'end': 208.308, 'src': 'embed', 'start': 186.415, 'weight': 4, 'content': [{'end': 194.901, 'text': 'Just check the video description for the download link and grab a copy of the files so that you can practice and follow along all the way through.', 'start': 186.415, 'duration': 8.486}, {'end': 197.362, 'text': 'Finally, at the end of the video,', 'start': 195.921, 'duration': 1.441}, {'end': 205.727, 'text': 'I will share some closing remarks on Power Query and provide some additional pointers for you if you want to learn and grow your Power Query journey.', 'start': 197.362, 'duration': 8.365}, {'end': 207.168, 'text': 'So continue.', 'start': 206.527, 'duration': 0.641}, {'end': 208.308, 'text': 'We have got a big plate.', 'start': 207.248, 'duration': 1.06}], 'summary': 'Video description has download link for practice files. closing remarks on power query at the end.', 'duration': 21.893, 'max_score': 186.415, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PiFAa_jjaEI/pics/PiFAa_jjaEI186415.jpg'}], 'start': 1.459, 'title': 'Power query tutorial', 'summary': 'Explores the powerful and useful features of power query, a tool that can automate data tasks, save time, and is transferable across excel and power bi, with examples showcasing data transformation and web scraping, along with examples of consolidation, merging, and appending tables, and access to sample datasets for practice.', 'chapters': [{'end': 140.369, 'start': 1.459, 'title': 'Power query tutorial', 'summary': 'Explores the powerful and useful features of power query, a life-changing tool that can automate common data tasks, save time, and is transferable across excel and power bi, with examples showcasing data transformation and web scraping.', 'duration': 138.91, 'highlights': ['Power Query can automate most common data collection, cleanup, extraction, transformation, and maintenance jobs, saving a ton of time for analysts or reporting professionals.', 'Learning Power Query is a transferable skill applicable in both Excel and Power BI, making it a valuable tool in data analysis.', 'The tutorial includes examples of cleaning up a made-up employee dataset and web scraping through Power Query, demonstrating its ability to connect to websites, scrape structured data, and maintain it for analysis.']}, {'end': 208.308, 'start': 140.789, 'title': 'Power query: examples and consolidation', 'summary': 'Discusses examples of consolidation in power query, including merging and appending tables, and provides access to sample datasets for practice, concluding with additional pointers for further learning.', 'duration': 67.519, 'highlights': ['The chapter explains consolidation in Power Query, covering merging and appending tables to create a comprehensive view of data.', 'It also emphasizes the availability of sample datasets and completed workbooks for download, enabling practical application and learning.', 'Furthermore, it concludes with additional pointers for individuals seeking to enhance their understanding of Power Query.']}], 'duration': 206.849, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PiFAa_jjaEI/pics/PiFAa_jjaEI1459.jpg', 'highlights': ['Power Query can automate most common data collection, cleanup, extraction, and transformation, saving time.', 'Learning Power Query is transferable across Excel and Power BI, making it a valuable tool in data analysis.', 'Examples include cleaning up a made-up employee dataset and web scraping through Power Query.', 'The chapter explains consolidation in Power Query, covering merging and appending tables.', 'Sample datasets and completed workbooks are available for practical application and learning.', 'Power Query provides access to websites, scrapes structured data, and maintains it for analysis.']}, {'end': 656.176, 'segs': [{'end': 250.063, 'src': 'embed', 'start': 220.942, 'weight': 0, 'content': [{'end': 228.287, 'text': 'Okay, so in this session, we are going to look at Power Query from a mind map point of view.', 'start': 220.942, 'duration': 7.345}, {'end': 234.652, 'text': "So I'm going to explain Power Query because Power Query is a fairly complex piece of software.", 'start': 228.768, 'duration': 5.884}, {'end': 240.096, 'text': 'Trying to understand it from a traditional way is really tricky.', 'start': 235.492, 'duration': 4.604}, {'end': 242.657, 'text': 'So I thought a mind map would be good.', 'start': 240.176, 'duration': 2.481}, {'end': 245.98, 'text': 'So I got my sketch board and I will be drawing.', 'start': 242.758, 'duration': 3.222}, {'end': 250.063, 'text': 'So we have Power Query as the central concept.', 'start': 246.44, 'duration': 3.623}], 'summary': 'Exploring power query through a mind map for better understanding.', 'duration': 29.121, 'max_score': 220.942, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PiFAa_jjaEI/pics/PiFAa_jjaEI220942.jpg'}, {'end': 374.991, 'src': 'embed', 'start': 345.24, 'weight': 3, 'content': [{'end': 349.302, 'text': 'And then finally, you load it into a different place from where data analysis happens.', 'start': 345.24, 'duration': 4.062}, {'end': 353.265, 'text': 'So Power Query is essentially an ETL application.', 'start': 349.322, 'duration': 3.943}, {'end': 365.435, 'text': 'But for us people who are working in Excel or Power BI, chances are we are not very keen on all the processes of ETL.', 'start': 354.225, 'duration': 11.21}, {'end': 371.02, 'text': 'So Power Query also helps us with other things like data cleaning or data cleansing.', 'start': 365.475, 'duration': 5.545}, {'end': 374.991, 'text': "Okay, so that's another application of Power Query.", 'start': 372.529, 'duration': 2.462}], 'summary': 'Power query serves as an etl tool for data analysis in excel and power bi, simplifying processes and facilitating data cleaning.', 'duration': 29.751, 'max_score': 345.24, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PiFAa_jjaEI/pics/PiFAa_jjaEI345240.jpg'}, {'end': 498.536, 'src': 'embed', 'start': 471.716, 'weight': 1, 'content': [{'end': 476.978, 'text': 'It is essentially an ETL tool, a data cleansing tool, a connector, a consolidator.', 'start': 471.716, 'duration': 5.262}, {'end': 482.48, 'text': 'You can also use Power Query for other purposes like data generation.', 'start': 478.078, 'duration': 4.402}, {'end': 489.967, 'text': 'okay, so you could think of power query as a generator or creator of data.', 'start': 483.76, 'duration': 6.207}, {'end': 491.228, 'text': 'when would you create data?', 'start': 489.967, 'duration': 1.261}, {'end': 498.536, 'text': 'for example, if you want to make a calendar table for your analysis, you can use power query to do that kind of work.', 'start': 491.228, 'duration': 7.308}], 'summary': 'Power query functions as an etl, data cleansing, and data creation tool, useful for generating tables like calendars.', 'duration': 26.82, 'max_score': 471.716, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PiFAa_jjaEI/pics/PiFAa_jjaEI471716.jpg'}, {'end': 617.988, 'src': 'embed', 'start': 590.894, 'weight': 2, 'content': [{'end': 597.178, 'text': 'this is where you can see what is happening to the current thing that is being worked on in power query.', 'start': 590.894, 'duration': 6.284}, {'end': 607.962, 'text': "and then, uh, you also have technically not, uh, like they're part of ribbon itself, but we tend to view them separately, which is, uh, you know,", 'start': 598.877, 'duration': 9.085}, {'end': 614.646, 'text': 'publish button, and then there is also a backstage screen where you can adjust power query settings.', 'start': 607.962, 'duration': 6.684}, {'end': 617.988, 'text': 'so this is how the ui generally looks.', 'start': 614.646, 'duration': 3.342}], 'summary': 'Power query provides a visual interface for data manipulation and settings adjustment in power bi.', 'duration': 27.094, 'max_score': 590.894, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PiFAa_jjaEI/pics/PiFAa_jjaEI590894.jpg'}], 'start': 220.942, 'title': 'Power query', 'summary': 'Delves into power query, outlining its complexity and purpose as an etl application, data cleansing tool, connector, consolidator, and data generation tool, and explains its role in data analysis, covering the etl process and user interface details.', 'chapters': [{'end': 278.239, 'start': 220.942, 'title': 'Power query mind map', 'summary': 'Explores power query from a mind map perspective, highlighting its complexity and purpose as an etl application.', 'duration': 57.297, 'highlights': ['Power Query is explained using a mind map approach due to its complexity and multiple purposes.', 'Power Query is pitched or provided as an ETL application at its core or basic level.']}, {'end': 656.176, 'start': 279.28, 'title': 'Understanding power query in data analysis', 'summary': 'Explains that etl involves the process of extracting, transforming, and loading data for reporting purposes, and power query serves as an etl application, a data cleansing tool, a connector, and a consolidator, with additional capabilities for data generation. it also provides insights into the user interface of power query, detailing its elements and functions.', 'duration': 376.896, 'highlights': ["Power Query's Functions and Capabilities The chapter illustrates Power Query as an ETL application, a data cleansing tool, a connector, and a consolidator, with additional features for data generation, providing a comprehensive understanding of its versatile functions in data analysis.", 'User Interface of Power Query It delves into the user interface of Power Query, describing its elements such as ribbon, preview grid, query span, steps and query settings, and additional features like the publish button and backstage screen, offering a detailed overview of its functionalities and navigation.']}], 'duration': 435.234, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PiFAa_jjaEI/pics/PiFAa_jjaEI220942.jpg', 'highlights': ['Power Query is explained using a mind map approach due to its complexity and multiple purposes.', 'The chapter illustrates Power Query as an ETL application, a data cleansing tool, a connector, and a consolidator, with additional features for data generation, providing a comprehensive understanding of its versatile functions in data analysis.', 'It delves into the user interface of Power Query, describing its elements such as ribbon, preview grid, query span, steps and query settings, and additional features like the publish button and backstage screen, offering a detailed overview of its functionalities and navigation.', 'Power Query is pitched or provided as an ETL application at its core or basic level.']}, {'end': 1563.769, 'segs': [{'end': 716.176, 'src': 'embed', 'start': 656.176, 'weight': 1, 'content': [{'end': 668.284, 'text': 'Power Query also offers a lot of things by just right-clicking on a selected item and using those right-click context menu options which I consider them part of UI,', 'start': 656.176, 'duration': 12.108}, {'end': 671.446, 'text': "but they're not really shown anywhere explicitly, but they are there.", 'start': 668.284, 'duration': 3.162}, {'end': 674.749, 'text': 'So we have all of these.', 'start': 672.627, 'duration': 2.122}, {'end': 675.929, 'text': "Now let's get into the ribbon.", 'start': 674.829, 'duration': 1.1}, {'end': 679.652, 'text': 'In the ribbon itself, there are three main ribbons.', 'start': 676.07, 'duration': 3.582}, {'end': 681.553, 'text': 'So we will make three bubbles.', 'start': 680.212, 'duration': 1.341}, {'end': 683.274, 'text': 'One is your home ribbon.', 'start': 681.993, 'duration': 1.281}, {'end': 691.718, 'text': 'the other is your transform ribbon and then the third one is add column ribbon.', 'start': 684.995, 'duration': 6.723}, {'end': 698.121, 'text': 'the home ribboning is where common operations and frequently used items are usually bunched together,', 'start': 691.718, 'duration': 6.403}, {'end': 702.063, 'text': 'and a lot of things about query management also go here.', 'start': 698.121, 'duration': 3.942}, {'end': 716.176, 'text': 'so, for example, within home ribbon you would find your save button or publish button and then you will find your query management,', 'start': 702.063, 'duration': 14.113}], 'summary': 'Power query has three main ribbons: home, transform, and add column.', 'duration': 60, 'max_score': 656.176, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PiFAa_jjaEI/pics/PiFAa_jjaEI656176.jpg'}, {'end': 1081.76, 'src': 'embed', 'start': 1049.607, 'weight': 3, 'content': [{'end': 1051.289, 'text': 'this language is called m language.', 'start': 1049.607, 'duration': 1.682}, {'end': 1058.949, 'text': 'It is fairly technical language and you can use M language to develop advanced things within Power Query.', 'start': 1052.566, 'duration': 6.383}, {'end': 1064.571, 'text': 'Fortunately, about 99% of the time you do not need to know M language.', 'start': 1059.489, 'duration': 5.082}, {'end': 1072.495, 'text': 'You can just use the UI features, right click left button options and menu options to do what you want.', 'start': 1065.072, 'duration': 7.423}, {'end': 1074.956, 'text': "So you don't really need to know M language.", 'start': 1072.535, 'duration': 2.421}, {'end': 1081.76, 'text': 'but it is handy to understand that there is actually a language behind all of this, and that is the m language.', 'start': 1075.416, 'duration': 6.344}], 'summary': 'M language is a technical tool within power query, but not essential for 99% of users.', 'duration': 32.153, 'max_score': 1049.607, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PiFAa_jjaEI/pics/PiFAa_jjaEI1049607.jpg'}, {'end': 1247.922, 'src': 'embed', 'start': 1222.067, 'weight': 4, 'content': [{'end': 1227.83, 'text': 'it is a step recorder and each step depends on the previous step, unless you make changes to the query in an advanced manner.', 'start': 1222.067, 'duration': 5.763}, {'end': 1234.779, 'text': 'So for this reason, when you are working within Power Query, you have to think about future.', 'start': 1229.417, 'duration': 5.362}, {'end': 1238.86, 'text': 'Everything that you do within Power Query.', 'start': 1236.639, 'duration': 2.221}, {'end': 1247.922, 'text': 'it may work while you are working on it, but because what you are building is a repeatable process, when you refresh Power Query, uh,', 'start': 1238.86, 'duration': 9.062}], 'summary': 'Power query requires thinking about future for repeatable process.', 'duration': 25.855, 'max_score': 1222.067, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PiFAa_jjaEI/pics/PiFAa_jjaEI1222067.jpg'}, {'end': 1297.508, 'src': 'embed', 'start': 1270.116, 'weight': 0, 'content': [{'end': 1273.899, 'text': 'so data sets within power query?', 'start': 1270.116, 'duration': 3.783}, {'end': 1275.58, 'text': 'what kind of things that can be connected to?', 'start': 1273.899, 'duration': 1.681}, {'end': 1278.021, 'text': 'again, time for a new pen?', 'start': 1275.58, 'duration': 2.441}, {'end': 1283.184, 'text': 'um, so you can connect to pretty much any kind of thing, especially within power bi.', 'start': 1278.021, 'duration': 5.163}, {'end': 1288.287, 'text': 'it offers even wider choice, but both excel and power bi are fairly flexible.', 'start': 1283.184, 'duration': 5.103}, {'end': 1297.508, 'text': 'so we will start off with simple things like excel, csv, and then you have your web connection.', 'start': 1288.287, 'duration': 9.221}], 'summary': 'Power query can connect to various data sets, including excel, csv, and web sources, providing flexibility in power bi and excel.', 'duration': 27.392, 'max_score': 1270.116, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PiFAa_jjaEI/pics/PiFAa_jjaEI1270116.jpg'}, {'end': 1405.13, 'src': 'embed', 'start': 1373.053, 'weight': 6, 'content': [{'end': 1377.016, 'text': 'Certain things like web and APIs require some authentication as well.', 'start': 1373.053, 'duration': 3.963}, {'end': 1381.018, 'text': 'For example, you may need to provide your user ID and password to get to a certain resource,', 'start': 1377.036, 'duration': 3.982}, {'end': 1384.68, 'text': 'and Power Query gives a structured way of providing those things as well.', 'start': 1381.018, 'duration': 3.662}, {'end': 1390.103, 'text': 'So you can provide your your account or you can tie it to your Windows login address as well.', 'start': 1384.7, 'duration': 5.403}, {'end': 1396.146, 'text': 'So that means it will use your Windows authentication to get to a data source and bring what you want.', 'start': 1390.143, 'duration': 6.003}, {'end': 1405.13, 'text': 'A classic example could be like an intranet page on SharePoint or some other source which can only be accessed when you connect to your organizational network,', 'start': 1396.566, 'duration': 8.564}], 'summary': 'Power query allows structured authentication for web and apis, including windows authentication for accessing data sources.', 'duration': 32.077, 'max_score': 1373.053, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PiFAa_jjaEI/pics/PiFAa_jjaEI1373053.jpg'}], 'start': 656.176, 'title': 'Power query essentials', 'summary': 'Covers power query essentials, including ribbons and operations, ui components, m language, and data connectivity options, enabling users to efficiently transform and connect to various data sources.', 'chapters': [{'end': 850.251, 'start': 656.176, 'title': 'Power query ribbons and operations', 'summary': 'Discusses the three main ribbons in power query, namely home, transform, and add column ribbons, each serving different purposes, with common operations and frequently used items bunched together, and various transformations and add column operations available.', 'duration': 194.075, 'highlights': ['The home ribbon contains common operations and frequently used items, including query management tasks such as merging multiple queries and common operations like moving the first row as header.', 'The transform ribbon and add column ribbon each serve different purposes, with the former focusing on operations that change the nature of the data itself, such as shape, text, number, date, and time transformations, while the latter allows for adding columns, including conditional columns and column from example.', 'Common operations within the transform ribbon involve changing the shape of data, text transformations, number transformations, and date and time-related transformations, while the add column ribbon facilitates actions such as adding conditional columns and teaching Power Query to build the desired output.']}, {'end': 1183.436, 'start': 850.831, 'title': 'Understanding power query ui and operations', 'summary': 'Covers power query ui, including the view ribbon, preview section, data grid, and queries pane, as well as important concepts such as m language, case sensitivity, and the step recorder functionality.', 'duration': 332.605, 'highlights': ['Power Query UI components explained The chapter explains the view ribbon, preview section, data grid, and queries pane in Power Query, providing an overview of their functionalities and settings.', 'Introduction to M language and its usage The chapter introduces M language as the technical language used within Power Query, highlighting its advanced development capabilities while emphasizing that 99% of tasks can be accomplished through UI features.', 'Significance of case sensitivity in Power Query It emphasizes the importance of case sensitivity within Power Query, unlike Excel or Power BI, where case does not affect operations, highlighting the need for careful attention to case in Power Query operations.', 'Understanding the step recorder functionality The chapter explains the step recorder as a tool that records operations in a step-by-step fashion, emphasizing its role as a data process automation tool and the dependency of each step on the previous one.']}, {'end': 1563.769, 'start': 1183.436, 'title': 'Power query tutorial', 'summary': 'Provides an overview of power query including its dependency on previous steps, the types of data sets it can connect to, and the need to anticipate future changes in data. it also discusses the flexibility of connecting to various data sources, including web pages, databases, apis, and cloud sources.', 'duration': 380.333, 'highlights': ['Power Query operates with a step-by-step dependency where each step is reliant on the previous one, highlighting the need to anticipate potential errors and changes in data to ensure the proper functioning of subsequent steps. ', 'Power Query offers flexibility in connecting to various data sets, including Excel, CSV, web pages, XML, JSON, APIs, databases (such as Oracle, SQL Server, and MySQL), cloud sources, folders, and PDFs, with continuous updates and additions to the available data connection options. ', 'Authentication requirements for web and API connections within Power Query, including the provision for user ID and password, as well as the utilization of Windows authentication for accessing data sources behind organizational network layers, are highlighted. ']}], 'duration': 907.593, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PiFAa_jjaEI/pics/PiFAa_jjaEI656176.jpg', 'highlights': ['Power Query offers flexibility in connecting to various data sets, including Excel, CSV, web pages, XML, JSON, APIs, databases, cloud sources, folders, and PDFs.', 'The home ribbon contains common operations and frequently used items, including query management tasks and common operations like moving the first row as header.', 'The transform ribbon and add column ribbon each serve different purposes, with the former focusing on operations that change the nature of the data itself, such as shape, text, number, date, and time transformations.', 'Introduction to M language and its usage, emphasizing that 99% of tasks can be accomplished through UI features.', 'Power Query operates with a step-by-step dependency where each step is reliant on the previous one, highlighting the need to anticipate potential errors and changes in data.', 'Power Query UI components explained, providing an overview of their functionalities and settings.', 'Authentication requirements for web and API connections within Power Query, including the provision for user ID and password, as well as the utilization of Windows authentication for accessing data sources behind organizational network layers.']}, {'end': 2203.762, 'segs': [{'end': 1776.194, 'src': 'embed', 'start': 1717.332, 'weight': 0, 'content': [{'end': 1719.894, 'text': 'This is a special window called Power Query Editor,', 'start': 1717.332, 'duration': 2.562}, {'end': 1728.801, 'text': 'and you can access this from both Excel and Power BI by using the queries button within the respective applications.', 'start': 1719.894, 'duration': 8.907}, {'end': 1736.987, 'text': 'So once you get here, there is all these ribbons, you can use those ribbons to access various functionalities of Power Query.', 'start': 1729.321, 'duration': 7.666}, {'end': 1745.697, 'text': 'The Power Query Editor window itself is split into basically, apart from the ribbon, there is three main areas.', 'start': 1738.493, 'duration': 7.204}, {'end': 1748.019, 'text': 'The left-hand side is queries area.', 'start': 1746.298, 'duration': 1.721}, {'end': 1752.542, 'text': 'This is usually collapsed, but you can expand this and you can see all the queries that are available.', 'start': 1748.059, 'duration': 4.483}, {'end': 1756.364, 'text': 'Right now, there is only one query, the connection to this file.', 'start': 1752.602, 'duration': 3.762}, {'end': 1762.808, 'text': 'By the end of this video, you will have four or five queries because we are going to demonstrate four different examples.', 'start': 1756.924, 'duration': 5.884}, {'end': 1765.61, 'text': 'So all of them will be listed here.', 'start': 1763.809, 'duration': 1.801}, {'end': 1769.652, 'text': 'and then you have the data set preview window here.', 'start': 1766.13, 'duration': 3.522}, {'end': 1772.313, 'text': 'this will show a preview of the data.', 'start': 1769.652, 'duration': 2.661}, {'end': 1776.194, 'text': 'usually the preview here is about top 1000 rows of the data.', 'start': 1772.313, 'duration': 3.881}], 'summary': 'Power query editor in excel and power bi has ribbons for various functionalities. it includes a queries area and a data set preview window, showing a preview of around 1000 rows of data.', 'duration': 58.862, 'max_score': 1717.332, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PiFAa_jjaEI/pics/PiFAa_jjaEI1717332.jpg'}, {'end': 1869.155, 'src': 'embed', 'start': 1841.505, 'weight': 4, 'content': [{'end': 1853.869, 'text': 'and then it automatically took the row number one and promoted that into headers and kind of done a data type changes for each column.', 'start': 1841.505, 'duration': 12.364}, {'end': 1855.349, 'text': 'So all of these steps were applied.', 'start': 1853.889, 'duration': 1.46}, {'end': 1860.371, 'text': 'If you are not happy with any of these steps, you can select a step and delete it.', 'start': 1856.37, 'duration': 4.001}, {'end': 1869.155, 'text': 'now keep in mind that power query is a step-by-step language, so every subsequent step by default depends on the previous step.', 'start': 1860.971, 'duration': 8.184}], 'summary': 'Power query automatically promoted row 1 into headers and performed data type changes for each column. users can delete steps and must consider the step-by-step language.', 'duration': 27.65, 'max_score': 1841.505, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PiFAa_jjaEI/pics/PiFAa_jjaEI1841505.jpg'}, {'end': 2016.963, 'src': 'embed', 'start': 1991.546, 'weight': 5, 'content': [{'end': 1998.951, 'text': 'So many of the functionalities within Power Query can be accessed through the ribbon or occasionally by right clicking.', 'start': 1991.546, 'duration': 7.405}, {'end': 2002.194, 'text': 'Again, we add another step, remove the columns.', 'start': 1999.852, 'duration': 2.342}, {'end': 2011.62, 'text': 'Now, usually whenever you promote the headers, whenever there is a new set of headers, Power Query automatically adds a data type change step.', 'start': 2002.734, 'duration': 8.886}, {'end': 2016.963, 'text': 'This can be both useful or useless depending on what your preference is.', 'start': 2013.081, 'duration': 3.882}], 'summary': 'Power query offers various functionalities through ribbon and right clicking. it automatically adds a data type change step when promoting headers, which can be useful or useless based on preference.', 'duration': 25.417, 'max_score': 1991.546, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PiFAa_jjaEI/pics/PiFAa_jjaEI1991546.jpg'}], 'start': 1563.769, 'title': 'Power query basics and introduction', 'summary': 'Covers the basics of loading and transforming data using power query in excel, emphasizing data access and transformation steps, along with specific tasks for data cleaning and preparation for analysis in excel.', 'chapters': [{'end': 1756.364, 'start': 1563.769, 'title': 'Power query basics', 'summary': 'Introduces the process of loading and transforming data using power query in excel, emphasizing the steps involved in accessing and transforming the data, and the functionalities and layout of the power query editor window.', 'duration': 192.595, 'highlights': ['The process involves accessing data from an external source, such as an Excel workbook, and then navigating through the Power Query Editor to preview and transform the data. Emphasizes the steps involved in accessing and transforming data using Power Query in Excel, highlighting the process of accessing data from an external source and navigating through the Power Query Editor to preview and transform the data.', 'The Power Query Editor window consists of three main areas: the queries area, the ribbons for accessing functionalities, and the data preview area for visualizing the data. Explains the layout of the Power Query Editor window, detailing the three main areas - queries area, ribbons for functionalities, and the data preview area for visualizing the data.', 'The Power Query Editor is accessible from both Excel and Power BI, and provides various functionalities through ribbons to manipulate and transform the data. Highlights the accessibility of the Power Query Editor from both Excel and Power BI, emphasizing its functionalities through ribbons to manipulate and transform the data.']}, {'end': 2203.762, 'start': 1756.924, 'title': 'Introduction to power query', 'summary': 'Explains the basics of power query, including data preview, applying steps, and data manipulation, and then outlines specific tasks for cleaning up the data, such as handling blanks and nulls, deleting employees with missing salaries, extracting country data, and preparing for analysis in excel.', 'duration': 446.838, 'highlights': ['The Power Query preview window displays a preview of the data, typically about the top 1000 rows, even if the original data set contains more rows.', 'The applied steps in Power Query are listed in the query settings area, allowing users to track and modify the operations performed on the data.', 'Power Query allows for step-by-step data manipulation, with subsequent steps depending on previous ones, enabling users to control the data processing flow.', 'The process of removing rows and columns, as well as promoting headers and changing data types, can be carried out in Power Query to clean up the data and prepare it for further analysis.', 'Specific tasks for data cleanup in Power Query include addressing blanks and nulls in the data, deleting employees with missing salaries, and extracting country information while ignoring location data.']}], 'duration': 639.993, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PiFAa_jjaEI/pics/PiFAa_jjaEI1563769.jpg', 'highlights': ['Emphasizes the steps involved in accessing and transforming data using Power Query in Excel, highlighting the process of accessing data from an external source and navigating through the Power Query Editor to preview and transform the data.', 'Explains the layout of the Power Query Editor window, detailing the three main areas - queries area, ribbons for functionalities, and the data preview area for visualizing the data.', 'Highlights the accessibility of the Power Query Editor from both Excel and Power BI, emphasizing its functionalities through ribbons to manipulate and transform the data.', 'The Power Query preview window displays a preview of the data, typically about the top 1000 rows, even if the original data set contains more rows.', 'Power Query allows for step-by-step data manipulation, with subsequent steps depending on previous ones, enabling users to control the data processing flow.', 'The process of removing rows and columns, as well as promoting headers and changing data types, can be carried out in Power Query to clean up the data and prepare it for further analysis.']}, {'end': 2965.766, 'segs': [{'end': 2229.196, 'src': 'embed', 'start': 2203.802, 'weight': 4, 'content': [{'end': 2211.187, 'text': "So once you do it, then it will remember that and it will use the same setting every time within Power Query, whichever workbook you're using.", 'start': 2203.802, 'duration': 7.385}, {'end': 2214.412, 'text': 'So we have replaced the gender.', 'start': 2212.492, 'duration': 1.92}, {'end': 2218.413, 'text': "Now let's go to the department and department has a null department.", 'start': 2214.492, 'duration': 3.921}, {'end': 2229.196, 'text': "And what happened is, let's just say, from a business point of view, we had some people join into a department when they joined the organization.", 'start': 2219.113, 'duration': 10.083}], 'summary': 'Power query remembers and uses same settings, replaces gender, addresses null department issue.', 'duration': 25.394, 'max_score': 2203.802, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PiFAa_jjaEI/pics/PiFAa_jjaEI2203802.jpg'}, {'end': 2608.012, 'src': 'heatmap', 'start': 2494.493, 'weight': 0.706, 'content': [{'end': 2499.334, 'text': "as long as you can eyeball the data and make sure that it is working, then that's there.", 'start': 2494.493, 'duration': 4.841}, {'end': 2502.155, 'text': 'This is added and it will be called custom.', 'start': 2499.994, 'duration': 2.161}, {'end': 2506.856, 'text': "I'm just going to double-click on the column header and rename that with country.", 'start': 2502.215, 'duration': 4.641}, {'end': 2509.576, 'text': 'This is how you name a column.', 'start': 2508.056, 'duration': 1.52}, {'end': 2511.357, 'text': 'Again, that will be added as a step.', 'start': 2509.617, 'duration': 1.74}, {'end': 2515.64, 'text': 'So once this is done, we will right click and then we will remove the original location.', 'start': 2511.917, 'duration': 3.723}, {'end': 2520.643, 'text': 'We no longer need the location full details because we have already extracted the country bit.', 'start': 2515.7, 'duration': 4.943}, {'end': 2528.848, 'text': 'And now all of our data is there and we can go ahead and start by closing and loading this data.', 'start': 2521.523, 'duration': 7.325}, {'end': 2531.71, 'text': 'So we will just say close and load.', 'start': 2530.149, 'duration': 1.561}, {'end': 2538.735, 'text': 'And this will now come up and sit as a nice table within Excel, all the data.', 'start': 2532.371, 'duration': 6.364}, {'end': 2551.254, 'text': 'So once this data is in Excel, I can go and build anything on top of it.', 'start': 2546.673, 'duration': 4.581}, {'end': 2556.515, 'text': 'For example, I could build a pivot table on top of this using summarize with pivot table option.', 'start': 2551.314, 'duration': 5.201}, {'end': 2563.296, 'text': 'And I could, for example, just see how much money we are paying by department and how many people are there.', 'start': 2557.215, 'duration': 6.081}, {'end': 2575.102, 'text': 'And then, for example, sort this largest to smallest and, you know, apply some currency format and things like that.', 'start': 2564.216, 'duration': 10.886}, {'end': 2578.784, 'text': 'so we are looking at all of our data and this is good.', 'start': 2575.102, 'duration': 3.682}, {'end': 2582.807, 'text': 'I can then even maybe add a slicer.', 'start': 2578.784, 'duration': 4.023}, {'end': 2590.211, 'text': "so I'm looking at either female employees, male employees or other employees and we are analyzing the data like this.", 'start': 2582.807, 'duration': 7.404}, {'end': 2592.313, 'text': "so let's just say all of this is good,", 'start': 2590.211, 'duration': 2.102}, {'end': 2608.012, 'text': 'we will rename this as pivot and and then we realize that all of this data is up until July 2020 and now we are already in August and we have brought in some more stuff and we would like to add their data.', 'start': 2592.313, 'duration': 15.699}], 'summary': 'Data is extracted, transformed, and loaded into excel, enabling analysis and visualization.', 'duration': 113.519, 'max_score': 2494.493, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PiFAa_jjaEI/pics/PiFAa_jjaEI2494493.jpg'}, {'end': 2688.562, 'src': 'embed', 'start': 2658.28, 'weight': 1, 'content': [{'end': 2661.261, 'text': 'all you have to do is go to data and refresh all.', 'start': 2658.28, 'duration': 2.981}, {'end': 2669.987, 'text': 'So you could see that now the new details have been added and 1, 002 people in 73 million is the total.', 'start': 2661.961, 'duration': 8.026}, {'end': 2675.672, 'text': 'So this is how the refresh process works and then the new details come through as well.', 'start': 2670.007, 'duration': 5.665}, {'end': 2679.875, 'text': 'So that is our very first example of Power Query.', 'start': 2676.212, 'duration': 3.663}, {'end': 2685.159, 'text': 'Let me just do a quick recap by showing you how to actually get back into the query and,', 'start': 2680.315, 'duration': 4.844}, {'end': 2688.562, 'text': 'if you change your mind or you want to modify a step or something, how to do that.', 'start': 2685.159, 'duration': 3.403}], 'summary': 'Refreshing data added 1,002 people in 73 million total, demonstrating power query.', 'duration': 30.282, 'max_score': 2658.28, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PiFAa_jjaEI/pics/PiFAa_jjaEI2658280.jpg'}, {'end': 2849.944, 'src': 'embed', 'start': 2814.834, 'weight': 2, 'content': [{'end': 2820.995, 'text': 'that data structure is not really suitable for analysis within Excel or Power BI.', 'start': 2814.834, 'duration': 6.161}, {'end': 2827.589, 'text': "So we would need to know exactly how to manipulate that within Power Query and that's what we will cover in the second example.", 'start': 2821.015, 'duration': 6.574}, {'end': 2849.944, 'text': 'in the second example of our power query, we will be looking at how you can connect to a website and fetch the data from the websites into excel.', 'start': 2837.569, 'duration': 12.375}], 'summary': 'Data manipulation in power query for website data retrieval.', 'duration': 35.11, 'max_score': 2814.834, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PiFAa_jjaEI/pics/PiFAa_jjaEI2814834.jpg'}, {'end': 2916.717, 'src': 'embed', 'start': 2874.56, 'weight': 0, 'content': [{'end': 2883.864, 'text': 'So you may want to connect to that and get some sort of a tabular data or list of items that are on that page and use that for further analysis.', 'start': 2874.56, 'duration': 9.304}, {'end': 2888.466, 'text': "So that's where the web data example comes in really handy,", 'start': 2884.344, 'duration': 4.122}, {'end': 2896.749, 'text': 'and this gives us a chance to really understand and appreciate the versatility and dynamic power of Power Query.', 'start': 2888.466, 'duration': 8.283}, {'end': 2907.153, 'text': "So for the purpose of this exercise, I'm going to get the data of Indian state population from Wikipedia.", 'start': 2897.649, 'duration': 9.504}, {'end': 2913.376, 'text': 'You could be using any website, as long as the website has some sort of data in a meaningful format.', 'start': 2907.453, 'duration': 5.923}, {'end': 2916.717, 'text': 'you can connect to that through Power Query and fetch that.', 'start': 2913.376, 'duration': 3.341}], 'summary': 'Utilize power query to extract and analyze web data, like indian state population from wikipedia.', 'duration': 42.157, 'max_score': 2874.56, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PiFAa_jjaEI/pics/PiFAa_jjaEI2874560.jpg'}], 'start': 2203.802, 'title': 'Power query data transformation and web data analysis', 'summary': 'Covers data transformation using power query, including null value replacement, filters, and data extraction. it also showcases examples of adding large datasets and manipulating web data, highlighting the dynamic power of power query.', 'chapters': [{'end': 2632.389, 'start': 2203.802, 'title': 'Power query data transformation', 'summary': 'Highlights the process of data transformation using power query, including replacing null values, applying filters, and extracting specific information from the dataset, with examples of data manipulation and the final step of loading the transformed data into excel for further analysis and visualization.', 'duration': 428.587, 'highlights': ['The process of data transformation using Power Query The speaker walks through the process of using Power Query to transform data, including steps such as replacing null values, applying filters, and extracting specific information from the dataset.', "Replacing null values in the dataset The speaker provides an example of replacing null values in the department field with the correct department, using the 'replace value' step in Power Query.", 'Applying filters to remove specific data from the dataset The speaker explains the process of applying a filter to remove data where the salary field is null, considering such employees as no longer active, and demonstrates the application of the filter step in Power Query.', "Extracting specific information from the dataset The speaker demonstrates the extraction of specific information, such as extracting the country name from the location field, using Power Query functions like 'extract last characters' and 'column from examples'.", 'Final step of loading transformed data into Excel for analysis and visualization The speaker concludes by showing the final step of loading the transformed data into Excel, enabling further analysis and visualization of the data, such as building pivot tables and applying various formatting options.']}, {'end': 2965.766, 'start': 2632.389, 'title': 'Power query examples and web data analysis', 'summary': 'Covers the use of power query to refresh and modify data, showcasing an example where 1,002 people with a total of 73 million were added, and later, the process of connecting to a website to fetch and manipulate data, emphasizing the versatility and dynamic power of power query.', 'duration': 333.377, 'highlights': ['The process of refreshing data in Power Query, where 1,002 people with a total of 73 million were added, is demonstrated, illustrating the practical application of the tool.', 'Demonstration of connecting to a website and fetching data for analysis, highlighting the versatility and dynamic power of Power Query.', 'The importance of understanding how to manipulate web data within Power Query to make it suitable for analysis in Excel or Power BI is emphasized for organizational data analysis purposes.', 'The example of fetching Indian state population data from Wikipedia using Power Query is used to showcase the potential of connecting to and fetching data from websites for analysis.']}], 'duration': 761.964, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PiFAa_jjaEI/pics/PiFAa_jjaEI2203802.jpg', 'highlights': ['Demonstration of connecting to a website and fetching data for analysis, highlighting the versatility and dynamic power of Power Query.', 'The process of refreshing data in Power Query, where 1,002 people with a total of 73 million were added, is demonstrated, illustrating the practical application of the tool.', 'The process of data transformation using Power Query The speaker walks through the process of using Power Query to transform data, including steps such as replacing null values, applying filters, and extracting specific information from the dataset.', 'The example of fetching Indian state population data from Wikipedia using Power Query is used to showcase the potential of connecting to and fetching data from websites for analysis.', "Replacing null values in the dataset The speaker provides an example of replacing null values in the department field with the correct department, using the 'replace value' step in Power Query."]}, {'end': 3469.339, 'segs': [{'end': 2998.37, 'src': 'embed', 'start': 2968.388, 'weight': 3, 'content': [{'end': 2973.813, 'text': 'and, as you could see, while this does have all the data, there are some potential issues with this data.', 'start': 2968.388, 'duration': 5.425}, {'end': 2986.323, 'text': 'for example, some states did not exist until certain point in time, so prior to that, their population is listed as n a, likewise some.', 'start': 2973.813, 'duration': 12.51}, {'end': 2991.827, 'text': 'the same goes for these states, and all of that information is there, okay.', 'start': 2986.323, 'duration': 5.504}, {'end': 2998.37, 'text': 'so we would like to, for example, go and connect to this web page,', 'start': 2991.827, 'duration': 6.543}], 'summary': 'Some data issues, such as missing state population and state existence, need to be addressed.', 'duration': 29.982, 'max_score': 2968.388, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PiFAa_jjaEI/pics/PiFAa_jjaEI2968388.jpg'}, {'end': 3072.626, 'src': 'embed', 'start': 3046.349, 'weight': 0, 'content': [{'end': 3053.672, 'text': "At this point, if this is the first time you're connecting to Wikipedia, Power Query may actually show you a message asking how you want to connect.", 'start': 3046.349, 'duration': 7.323}, {'end': 3059.594, 'text': 'I have already done this a few times, so it kind of jumps in here and it will show you the web page.', 'start': 3053.752, 'duration': 5.842}, {'end': 3063.755, 'text': 'And Power Query says I found three different tables on that web page.', 'start': 3059.754, 'duration': 4.001}, {'end': 3068.961, 'text': 'Which one do you want? You can kind of select them to see which one has the data that we want.', 'start': 3063.815, 'duration': 5.146}, {'end': 3072.626, 'text': 'For example, that table, the very first one is what we really want.', 'start': 3069.001, 'duration': 3.625}], 'summary': 'Power query finds 3 tables, selects the first one with desired data.', 'duration': 26.277, 'max_score': 3046.349, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PiFAa_jjaEI/pics/PiFAa_jjaEI3046349.jpg'}, {'end': 3261.716, 'src': 'embed', 'start': 3238.907, 'weight': 2, 'content': [{'end': 3247.55, 'text': 'we just want to take this kind of a pivot table format and unpivot this so that we want to keep this column and for every other column.', 'start': 3238.907, 'duration': 8.643}, {'end': 3252.712, 'text': 'we just want to turn this into two columns one is year and the other is population.', 'start': 3247.55, 'duration': 5.162}, {'end': 3256.753, 'text': 'so this kind of an operation is called transform unpivot,', 'start': 3252.712, 'duration': 4.041}, {'end': 3261.716, 'text': 'because we are changing the nature of the data And you can do this from the transform ribbon.', 'start': 3256.753, 'duration': 4.963}], 'summary': 'Unpivot pivot table to two columns: year and population.', 'duration': 22.809, 'max_score': 3238.907, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PiFAa_jjaEI/pics/PiFAa_jjaEI3238907.jpg'}, {'end': 3429.419, 'src': 'embed', 'start': 3400.869, 'weight': 1, 'content': [{'end': 3402.87, 'text': 'other is, you can replace the errors.', 'start': 3400.869, 'duration': 2.001}, {'end': 3408.552, 'text': "so if you don't want to have those state year combinations when the state didn't exist, you can remove the errors.", 'start': 3402.87, 'duration': 5.682}, {'end': 3415.894, 'text': 'Alternatively, if you want to do some analysis where you want to have the presence of that, you can also replace them with zero.', 'start': 3409.512, 'duration': 6.382}, {'end': 3417.515, 'text': "I'm just going to remove them.", 'start': 3416.274, 'duration': 1.241}, {'end': 3419.556, 'text': 'That will make their data tidy.', 'start': 3418.095, 'duration': 1.461}, {'end': 3426.378, 'text': 'And then finally, before we publish this, I want to rename this as pop.', 'start': 3420.256, 'duration': 6.122}, {'end': 3429.419, 'text': 'or india.', 'start': 3428.899, 'duration': 0.52}], 'summary': 'Remove errors to tidy data before renaming as pop or india.', 'duration': 28.55, 'max_score': 3400.869, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PiFAa_jjaEI/pics/PiFAa_jjaEI3400869.jpg'}], 'start': 2968.388, 'title': 'Data analysis challenges and importing and transforming data from wikipedia', 'summary': 'Discusses challenges of dealing with incomplete data, particularly related to population counts in states, emphasizing the need to transform table structure. it also explains importing and transforming data from wikipedia using power query in excel, including removing unnecessary rows, unpivoting columns, and addressing errors.', 'chapters': [{'end': 3020.141, 'start': 2968.388, 'title': 'Data analysis challenges', 'summary': 'Discusses the challenges of dealing with incomplete data, particularly related to population counts in certain states, and the need to transform the table structure for effective analysis.', 'duration': 51.753, 'highlights': ["The challenge of dealing with incomplete data in certain states, where their population is listed as 'n a'", 'The need to connect to a web page to retrieve missing data and transform the table structure for effective analysis']}, {'end': 3469.339, 'start': 3020.141, 'title': 'Importing and transforming data from wikipedia', 'summary': 'Explains how to import and transform data from wikipedia using power query in excel, including removing unnecessary rows, unpivoting columns to create a tidy data structure, and addressing errors in the data.', 'duration': 449.198, 'highlights': ['The chapter explains how to import and transform data from Wikipedia using Power Query in Excel. It involves importing data from Wikipedia using Power Query in Excel, which allows for transformations and cleaning of the data.', "Removing unnecessary rows and addressing errors in the data. The process includes removing repeated header and grand total rows, as well as addressing 'NA' values to ensure a tidy dataset.", 'Unpivoting columns to create a tidy data structure. The tutorial demonstrates how to unpivot columns to create a more organized and structured data format, specifically transforming the data into a pivot table format and unpivoting it to create two columns for year and population.']}], 'duration': 500.951, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PiFAa_jjaEI/pics/PiFAa_jjaEI2968388.jpg', 'highlights': ['The need to connect to a web page to retrieve missing data and transform the table structure for effective analysis', "Removing unnecessary rows and addressing errors in the data, including 'NA' values, to ensure a tidy dataset", 'Unpivoting columns to create a tidy data structure, transforming the data into a pivot table format and unpivoting it to create two columns for year and population', "The challenge of dealing with incomplete data in certain states, where their population is listed as 'n a'", 'The chapter explains how to import and transform data from Wikipedia using Power Query in Excel, involving importing data from Wikipedia using Power Query in Excel, which allows for transformations and cleaning of the data']}, {'end': 4134.233, 'segs': [{'end': 3580.395, 'src': 'embed', 'start': 3492.483, 'weight': 0, 'content': [{'end': 3496.044, 'text': 'The assumptions are that this table will be there and it will be in this format.', 'start': 3492.483, 'duration': 3.561}, {'end': 3500.026, 'text': 'But if that changes, then your power query will no longer work.', 'start': 3496.465, 'duration': 3.561}, {'end': 3505.508, 'text': 'So that is something that you want to keep in mind all the time when you are working with web sources.', 'start': 3500.506, 'duration': 5.002}, {'end': 3512.67, 'text': "This is why at the beginning I said in strict business data analysis, you don't normally use like this kind of a connection.", 'start': 3506.028, 'duration': 6.642}, {'end': 3518.939, 'text': "But let's say you're connecting to a SharePoint web page and the data is always maintained in that format.", 'start': 3513.291, 'duration': 5.648}, {'end': 3521.643, 'text': 'You can always use this method and get to it.', 'start': 3519.359, 'duration': 2.284}, {'end': 3527.591, 'text': "So that's our second example of our query.", 'start': 3522.384, 'duration': 5.207}, {'end': 3539.1, 'text': 'In the third example of our Power Query tutorial,', 'start': 3536.417, 'duration': 2.683}, {'end': 3548.11, 'text': 'we are going to look at how to take a folder of all files and consolidate them into one big table using Power Query.', 'start': 3539.1, 'duration': 9.01}, {'end': 3557.641, 'text': 'For this example, we are going to look at a bunch of project Excel files and combine the data into one final set of data.', 'start': 3548.671, 'duration': 8.97}, {'end': 3568.609, 'text': 'You can use this technique to combine data that is in text files, CSV files, even PDFs through Power Query.', 'start': 3559.284, 'duration': 9.325}, {'end': 3572.251, 'text': 'So here is our folder of files.', 'start': 3570.19, 'duration': 2.061}, {'end': 3576.753, 'text': 'I got four files here, each having the same pattern.', 'start': 3572.331, 'duration': 4.422}, {'end': 3580.395, 'text': 'They just list by month how much amount is spent on the project.', 'start': 3576.913, 'duration': 3.482}], 'summary': 'Power query can consolidate files into one table. can be used for sharepoint data.', 'duration': 87.912, 'max_score': 3492.483, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PiFAa_jjaEI/pics/PiFAa_jjaEI3492483.jpg'}, {'end': 3793.037, 'src': 'embed', 'start': 3765.64, 'weight': 3, 'content': [{'end': 3774.446, 'text': 'But what we started off was we loaded a sample file and then we taught Power Query on that sample file, get to the sheet one.', 'start': 3765.64, 'duration': 8.806}, {'end': 3782.591, 'text': 'So, based on the sample file, Power Query builds a small internal query called transform sample file,', 'start': 3774.946, 'duration': 7.645}, {'end': 3785.292, 'text': 'where the operations that you want to do are defined.', 'start': 3782.591, 'duration': 2.701}, {'end': 3793.037, 'text': 'So once these are done based on this Power Query will then create a transform file function.', 'start': 3785.933, 'duration': 7.104}], 'summary': 'Power query creates a transform file function based on loaded sample file.', 'duration': 27.397, 'max_score': 3765.64, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PiFAa_jjaEI/pics/PiFAa_jjaEI3765640.jpg'}, {'end': 4104.863, 'src': 'embed', 'start': 4072.874, 'weight': 4, 'content': [{'end': 4074.896, 'text': 'So this is how that thing is done.', 'start': 4072.874, 'duration': 2.022}, {'end': 4082.642, 'text': 'You can go and change the transform sample file again and remove this column and that will automatically update this particular outcome here as well.', 'start': 4075.516, 'duration': 7.126}, {'end': 4086.825, 'text': "So now let's go and close and load this and load this data here.", 'start': 4083.182, 'duration': 3.643}, {'end': 4090.065, 'text': 'All the data comes through very nicely.', 'start': 4088.263, 'duration': 1.802}, {'end': 4098.935, 'text': 'Of course this date is shown here as a number because Excel dates are numbers, but I can select this and quickly turn that into a date format,', 'start': 4090.125, 'duration': 8.81}, {'end': 4100.438, 'text': 'and our data is all good to go.', 'start': 4098.935, 'duration': 1.503}, {'end': 4104.863, 'text': "Now let's go ahead and see what happens if you add another file to the folder.", 'start': 4101.018, 'duration': 3.845}], 'summary': 'Transformed sample file updates outcome; data loads smoothly.', 'duration': 31.989, 'max_score': 4072.874, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PiFAa_jjaEI/pics/PiFAa_jjaEI4072874.jpg'}, {'end': 4147.408, 'src': 'embed', 'start': 4114.322, 'weight': 5, 'content': [{'end': 4116.764, 'text': "Let's see what happens on this page here.", 'start': 4114.322, 'duration': 2.442}, {'end': 4118.366, 'text': "So right now it doesn't have project new.", 'start': 4116.863, 'duration': 1.503}, {'end': 4122.488, 'text': 'And if I go to data, you can refresh all the queries.', 'start': 4118.906, 'duration': 3.582}, {'end': 4129.595, 'text': 'or if you just want to refresh this particular query without touching the others, you can right click on the CSV query and refresh that.', 'start': 4122.488, 'duration': 7.107}, {'end': 4134.233, 'text': 'This will now be refreshed and the project new is listed right on the top,', 'start': 4130.689, 'duration': 3.544}, {'end': 4140.319, 'text': "because it's alphabetically the very first project and the data is coming through very nicely.", 'start': 4134.233, 'duration': 6.086}, {'end': 4147.408, 'text': 'So this is how Power Query can be used to consolidate data that is all in a folder and bring that together.', 'start': 4140.901, 'duration': 6.507}], 'summary': 'Using power query to consolidate and refresh data, resulting in successful integration of new project data.', 'duration': 33.086, 'max_score': 4114.322, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PiFAa_jjaEI/pics/PiFAa_jjaEI4114322.jpg'}], 'start': 3469.899, 'title': 'Power query in data processing', 'summary': 'Covers the importance of format and assumptions when working with web sources, connecting to a sharepoint web page, consolidating data from files into a table, data transformation with power query, including adding custom columns, filtering file types, and automatic data updates.', 'chapters': [{'end': 3821.12, 'start': 3469.899, 'title': 'Power query tutorial', 'summary': 'Discusses the importance of being mindful about the format and assumptions when working with web sources and provides examples of connecting to a sharepoint web page and consolidating data from a folder of files into one big table using power query.', 'duration': 351.221, 'highlights': ['Power Query requires being mindful about the format and assumptions when working with web sources to ensure correct web connection and query, as format changes can lead to non-functioning queries. It is essential to be mindful about the format and assumptions when working with web sources to ensure correct web connection and query, as changes in format can render the power query non-functional.', 'Connecting to a SharePoint web page and maintaining data in a consistent format allows for the use of Power Query, demonstrating a practical application of the method. Connecting to a SharePoint web page and maintaining data in a consistent format allows for the use of Power Query, demonstrating a practical application of the method.', 'Consolidating data from a folder of files into one big table using Power Query, allowing for the combination of data from various sources such as text files, CSV files, and PDFs. Demonstrates the process of consolidating data from a folder of files into one big table using Power Query, allowing for the combination of data from various sources such as text files, CSV files, and PDFs.']}, {'end': 4134.233, 'start': 3822.992, 'title': 'Power query data transformation', 'summary': 'Explains how to use power query to transform and manipulate data, including adding custom columns, filtering file types, and updating data automatically, resulting in efficient and dynamic data processing.', 'duration': 311.241, 'highlights': ['Power Query allows for easy data transformation and manipulation, including adding custom columns and filtering file types. Custom columns, file type filtering', 'Automatic update of data is achieved by making changes in the transform sample file, ensuring efficiency and dynamic data processing. Automatic data update', 'Refreshing the queries enables the display of newly added files and updates in the data set, demonstrating the flexibility and efficiency of Power Query. Query refreshing, updated data display']}], 'duration': 664.334, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PiFAa_jjaEI/pics/PiFAa_jjaEI3469899.jpg', 'highlights': ['Consolidating data from a folder of files into one big table using Power Query, allowing for the combination of data from various sources such as text files, CSV files, and PDFs.', 'Connecting to a SharePoint web page and maintaining data in a consistent format allows for the use of Power Query, demonstrating a practical application of the method.', 'Power Query requires being mindful about the format and assumptions when working with web sources to ensure correct web connection and query, as format changes can lead to non-functioning queries.', 'Power Query allows for easy data transformation and manipulation, including adding custom columns and filtering file types.', 'Automatic update of data is achieved by making changes in the transform sample file, ensuring efficiency and dynamic data processing.', 'Refreshing the queries enables the display of newly added files and updates in the data set, demonstrating the flexibility and efficiency of Power Query.']}, {'end': 5091.361, 'segs': [{'end': 4199.206, 'src': 'embed', 'start': 4169.265, 'weight': 4, 'content': [{'end': 4174.687, 'text': 'This is actually a very powerful and helpful feature of Power Query,', 'start': 4169.265, 'duration': 5.422}, {'end': 4183.85, 'text': 'where you can take two sets of data and then either join them or combine the tables to get one full set of data.', 'start': 4174.687, 'duration': 9.163}, {'end': 4194.884, 'text': "So for this example, I have some sample data here where we know the student's information from year 11 and 12.", 'start': 4185.478, 'duration': 9.406}, {'end': 4199.206, 'text': 'We know the names, gender and which course the student has signed up for.', 'start': 4194.884, 'duration': 4.322}], 'summary': 'Power query can join or combine two sets of data to create one full set, demonstrated with student data from year 11 and 12.', 'duration': 29.941, 'max_score': 4169.265, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PiFAa_jjaEI/pics/PiFAa_jjaEI4169265.jpg'}, {'end': 4299.391, 'src': 'embed', 'start': 4252.781, 'weight': 1, 'content': [{'end': 4263.171, 'text': 'Then we want to combine that appended data with the course information so that for each student I can see, for example, who is their teacher,', 'start': 4252.781, 'duration': 10.39}, {'end': 4270.678, 'text': 'what kind of lesson they are going to take in that course and how many credits they are going to get, and what kind of assessment it is going to be.', 'start': 4263.171, 'duration': 7.507}, {'end': 4272.138, 'text': 'all right.', 'start': 4271.238, 'duration': 0.9}, {'end': 4280.743, 'text': 'and then, finally, we will also want to filter all the female students that are on three or more credit courses.', 'start': 4272.138, 'duration': 8.605}, {'end': 4284.564, 'text': 'so anybody who is on the courses that have three credits or four credits,', 'start': 4280.743, 'duration': 3.821}, {'end': 4290.927, 'text': 'we would like to filter them down and see such student data and publish that back to excel.', 'start': 4284.564, 'duration': 6.363}, {'end': 4296.05, 'text': "so i'll close this file and let's go here and bring the data.", 'start': 4290.927, 'duration': 5.123}, {'end': 4297.19, 'text': 'so we go to data.', 'start': 4296.05, 'duration': 1.14}, {'end': 4299.391, 'text': 'this time the data is in excel file.', 'start': 4297.19, 'duration': 2.201}], 'summary': 'Combine appended data with course info, filter female students on 3+ credit courses, and publish to excel.', 'duration': 46.61, 'max_score': 4252.781, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PiFAa_jjaEI/pics/PiFAa_jjaEI4252781.jpg'}, {'end': 4510.95, 'src': 'embed', 'start': 4485.396, 'weight': 0, 'content': [{'end': 4494.541, 'text': 'at this point the data will be appended and then I can also see whether somebody is an year 11 student or 12 student right in the final data.', 'start': 4485.396, 'duration': 9.145}, {'end': 4506.468, 'text': 'so now that this is all appended, we can just go and rename this table as students, because this is no longer year 11 students.', 'start': 4494.541, 'duration': 11.927}, {'end': 4510.95, 'text': "it has everybody's information and this has my student data.", 'start': 4506.468, 'duration': 4.482}], 'summary': "Appended data includes information for year 11 and 12 students, renamed table to 'students'.", 'duration': 25.554, 'max_score': 4485.396, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PiFAa_jjaEI/pics/PiFAa_jjaEI4485396.jpg'}, {'end': 4587.488, 'src': 'embed', 'start': 4560.36, 'weight': 3, 'content': [{'end': 4564.542, 'text': "normally within sql it is called join, but within power query it's called merge.", 'start': 4560.36, 'duration': 4.182}, {'end': 4566.083, 'text': "it's the same thing.", 'start': 4564.542, 'duration': 1.541}, {'end': 4573.843, 'text': 'so we can click on the students table much and select the courses table.', 'start': 4566.083, 'duration': 7.76}, {'end': 4578.124, 'text': 'now, when you are merging, you must specify what is the column on which you want to merge.', 'start': 4573.843, 'duration': 4.281}, {'end': 4583.206, 'text': 'so within the student, course column corresponds to courses table course column.', 'start': 4578.124, 'duration': 5.082}, {'end': 4585.067, 'text': 'so we are merging based on that column.', 'start': 4583.206, 'duration': 1.861}, {'end': 4587.488, 'text': 'you just select that and it will apply.', 'start': 4585.067, 'duration': 2.421}], 'summary': 'In power query, merging is like joining in sql. specify columns to merge.', 'duration': 27.128, 'max_score': 4560.36, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PiFAa_jjaEI/pics/PiFAa_jjaEI4560360.jpg'}, {'end': 4756.35, 'src': 'embed', 'start': 4727.958, 'weight': 5, 'content': [{'end': 4734.341, 'text': 'At this point, we would like to do the third operation, which is filter female students on three or more credit courses.', 'start': 4727.958, 'duration': 6.383}, {'end': 4743.044, 'text': 'So what I will do is instead of directly filtering in place here, we will keep the main set of students table as it is.', 'start': 4734.881, 'duration': 8.163}, {'end': 4745.665, 'text': 'I will create a reference to this.', 'start': 4743.605, 'duration': 2.06}, {'end': 4749.327, 'text': 'So this is actually another table, but it refers to the original table.', 'start': 4746.166, 'duration': 3.161}, {'end': 4756.35, 'text': 'So whatever original table contains, this will also contain female students.', 'start': 4749.347, 'duration': 7.003}], 'summary': 'Filter female students on three or more credit courses without altering the main student table.', 'duration': 28.392, 'max_score': 4727.958, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PiFAa_jjaEI/pics/PiFAa_jjaEI4727958.jpg'}, {'end': 5078.657, 'src': 'embed', 'start': 5053.838, 'weight': 7, 'content': [{'end': 5060.928, 'text': 'and I highly recommend that because it is very useful and it will help you learn Power Query more in a holistic manner.', 'start': 5053.838, 'duration': 7.09}, {'end': 5063.291, 'text': 'There is a link for the book in the video description.', 'start': 5061.148, 'duration': 2.143}, {'end': 5064.152, 'text': 'Please check that out.', 'start': 5063.351, 'duration': 0.801}, {'end': 5070.115, 'text': 'now in terms of courses, i recommend my excel school online training program again.', 'start': 5064.753, 'duration': 5.362}, {'end': 5078.657, 'text': 'this is very, very useful and it will help you understand and appreciate power query, along with the overall spectrum of data analysis and reporting.', 'start': 5070.115, 'duration': 8.542}], 'summary': 'The power query book and excel school online training program are recommended for holistic learning and understanding of power query and data analysis.', 'duration': 24.819, 'max_score': 5053.838, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PiFAa_jjaEI/pics/PiFAa_jjaEI5053838.jpg'}], 'start': 4134.233, 'title': 'Power query for data consolidation and merging', 'summary': 'Demonstrates using power query to consolidate student data from different tabs, resulting in a combined table of 301 student entries, and filtering female students taking three or more credit courses for publishing back to excel. it also explains how to combine and identify data from multiple tables, creating a unified table with custom columns, resulting in a comprehensive student information table. additionally, the chapter focuses on merging tables, applying different types of joins, and filtering female students on three or more credit courses, while presenting four examples of power query usage.', 'chapters': [{'end': 4299.391, 'start': 4134.233, 'title': 'Power query: consolidating and joining data', 'summary': 'Demonstrates the use of power query to consolidate student data from different tabs, append and merge tables, resulting in a combined table of 301 student entries, and filter female students taking three or more credit courses for publishing back to excel.', 'duration': 165.158, 'highlights': ['Consolidating and Appending Student Data The chapter showcases the process of appending student data from year 11 and year 12 into one table, resulting in a combined table of 301 student entries.', 'Merging and Combining Tables The tutorial illustrates the merging of student data with course information, enabling the visualization of teacher details, lesson types, credits, and assessment for each student.', 'Filtering Female Students on Credit Courses The chapter concludes by highlighting the filtering of female students enrolled in courses with three or more credits, aiming to publish the corresponding data back to Excel.']}, {'end': 4539.172, 'start': 4299.391, 'title': 'Combining and identifying student data', 'summary': 'Explains how to use power query to combine and identify data from multiple tables, creating a unified table with custom columns to differentiate between year 11 and year 12 students and fixing column headers, resulting in a comprehensive student information table.', 'duration': 239.781, 'highlights': ['Using Power Query to combine year 11 and year 12 data into one table Demonstrates the process of appending queries to merge data from different tables', 'Fixing column headers and differentiating between year 11 and year 12 students using custom columns Illustrates the method of adding custom columns to differentiate between student categories', "Renaming the combined table as 'students' and understanding course table details Shows the process of renaming the table and understanding the course table details"]}, {'end': 5091.361, 'start': 4539.172, 'title': 'Power query merging and filtering', 'summary': 'Focuses on using power query for merging tables and filtering data, including merging based on specific columns, applying different types of joins, and filtering female students on three or more credit courses, while presenting four examples of power query usage and providing recommendations for further learning.', 'duration': 552.189, 'highlights': ['The chapter explains the process of merging tables using Power Query, including joining based on specific columns and the different types of joins available, such as left outer and inner joins. It details the process of merging tables using Power Query, specifying the column on which to merge, and explains left outer and inner joins, providing clear understanding of the merging process.', 'It demonstrates filtering female students on three or more credit courses in Power Query, creating a new table with the filtered data and structuring support queries for organization. It demonstrates the process of filtering female students on three or more credit courses, creating a new table, and organizing support queries for efficient data manipulation and analysis.', 'The chapter concludes with recommendations for further learning, suggesting the application of presented concepts to real data sets, followed by resources such as a recommended Power Query book and an Excel school online training program. It concludes with recommendations for further learning, emphasizing the application of presented concepts to real data sets, and provides resources for additional learning through a recommended book and an online training program.']}], 'duration': 957.128, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PiFAa_jjaEI/pics/PiFAa_jjaEI4134233.jpg', 'highlights': ['Consolidating and Appending Student Data The chapter showcases the process of appending student data from year 11 and year 12 into one table, resulting in a combined table of 301 student entries.', 'Merging and Combining Tables The tutorial illustrates the merging of student data with course information, enabling the visualization of teacher details, lesson types, credits, and assessment for each student.', 'Filtering Female Students on Credit Courses The chapter concludes by highlighting the filtering of female students enrolled in courses with three or more credits, aiming to publish the corresponding data back to Excel.', 'The chapter explains the process of merging tables using Power Query, including joining based on specific columns and the different types of joins available, such as left outer and inner joins. It details the process of merging tables using Power Query, specifying the column on which to merge, and explains left outer and inner joins, providing clear understanding of the merging process.', 'Using Power Query to combine year 11 and year 12 data into one table Demonstrates the process of appending queries to merge data from different tables', 'It demonstrates filtering female students on three or more credit courses in Power Query, creating a new table with the filtered data and structuring support queries for organization. It demonstrates the process of filtering female students on three or more credit courses, creating a new table, and organizing support queries for efficient data manipulation and analysis.', 'Merging and Combining Tables The tutorial illustrates the merging of student data with course information, enabling the visualization of teacher details, lesson types, credits, and assessment for each student.', 'The chapter concludes with recommendations for further learning, suggesting the application of presented concepts to real data sets, followed by resources such as a recommended Power Query book and an Excel school online training program. It concludes with recommendations for further learning, emphasizing the application of presented concepts to real data sets, and provides resources for additional learning through a recommended book and an online training program.']}], 'highlights': ['Power Query can automate most common data collection, cleanup, extraction, and transformation, saving time.', 'Learning Power Query is transferable across Excel and Power BI, making it a valuable tool in data analysis.', 'Power Query offers flexibility in connecting to various data sets, including Excel, CSV, web pages, XML, JSON, APIs, databases, cloud sources, folders, and PDFs.', 'Power Query provides access to websites, scrapes structured data, and maintains it for analysis.', 'Demonstration of connecting to a website and fetching data for analysis, highlighting the versatility and dynamic power of Power Query.', 'Consolidating data from a folder of files into one big table using Power Query, allowing for the combination of data from various sources such as text files, CSV files, and PDFs.', 'Consolidating and Appending Student Data The chapter showcases the process of appending student data from year 11 and year 12 into one table, resulting in a combined table of 301 student entries.', 'Merging and Combining Tables The tutorial illustrates the merging of student data with course information, enabling the visualization of teacher details, lesson types, credits, and assessment for each student.']}