Excel BI - Power Query 1 | Paula Guilfoyle | Skillshare
Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
4 Lessons (12m)
    • 1. Introduction Excel BI Power Query

      3:00
    • 2. How to take this course

      1:23
    • 3. Getting set up with Power Query

      6:37
    • 4. Conclusion and whats next

      0:54

About This Class

Power Query enhances self-service business intelligence (BI) for Excel with an intuitive and consistent experience for discovering, combining, and refining data across a wide variety of sources including relational, structured and semi-structured, OData, Web, Hadoop, Azure Marketplace, and more.

Power Query brings the power of Data to the ordinary business and the Excel user.

COURSE SUMMARY

Power Query is an Excel add-in, available in Excel 2010 Professional Plus and Excel 2013. In Excel 2016 it is known as Get and discover data. Power Query can be described as SQL for Excel users (without the need for knowing SQL). It allows uses find data from multiple sources, combine data, transform data, and make the data usable for reporting and data analytics.

LEARN HOW SET UP QUERIES TO EXTRACT DATA FROM MULTIPLE SOURCES, TRANSFORM DATA OF DIFFERENT TYPES AND LEARN HOW TO USE POWER QUERY TO AUTOMATE DAILY TASKS.

This course is broken down into 3 classes.

In this, the first class, we will start at the very basics and learn how to set up Power Query. You will also find the sample data required for the next few classes.

The second class of this course works though a case study on how an organisation can use internal data of different file types, such as Text files and Excel files, and then combine this data with External data from the web to accurately calculate the potential market size, the actual market size and the portion of the market held by the company.  This case study will give you a solid working knowledge of Power Query.

In the Final Class we will look at some more advanced features of Power Query.  You will be introduced to the language use, which is known as M, you will learn how to query not just files of different types but also Folders of data. You will also learn how to sort “Bad Data” and user Power Query to automate reoccurring tasks.

Transcripts

1. Introduction Excel BI Power Query: para query is the first component of Microsoft South service. The I System, along with power pivot on power view. It's a free idea that's available for Excel to present in 10. I'm sure hasn't. And 13 in Excel 2000 and 16. It's built into Nolan's Get four para Query will allow you to search for or extract out of from many sources internal sources of external sources on different types of data, such as Excel files, folders of Dad, Jason funds, text files, SQL servers on on long data, you convention feel to true the data by applying queer kind of like SQL along techniques and then just pull back the Dodger that you need. You can clean the data. You could transform the data, and you could combine the data with all their data sources that are relevant. This data is then stored in a connection. I'm loaded into Excel or into an Excel model for further manipulation and analysis. With Excel would para pivot on visualize agent with power view. And at the end of the day, no matter what people say excel, it is the foundation of most businesses, even when they have complex software assistance people export of it into exam I manipulated on analyzer. But this can be time consuming, although some people have written BB eight codes to make recurring tasks. Room which faster, But it's even more time consuming. If you're adding data from outside of the company or the organization with para query excited uses, conceive which the power of diamond like never before. How quickly will you get data from almost any source, including social media? Opened. I had Big Jack sources on, then quickly transformed the data into a used before my in Excel chill that we were all so familiar with. Not only that, Herock worry will allow you to do repetitive tasks such as speeding and marriage and colors . Removing Commons, replacing text so so much more with just the click of a button on these queries. Convention saved. I'm were used over and over. In this course, you learn how to use Power query to connect different data sources, both internal and external. You learn how to query the down so you only return what you need and you'll also learn him . You could transform that data into a usable format for further analysis, reporting visualizations to get valuable insights for your organization 2. How to take this course: this course is broken down into three sections. This action is an introduction section and you learn him to download and install heroic worry and have at the power query written in section two of this course you don't have to use power query by Use off a case study. In this scenario, the case study scenario will be presented to before we get stuck into the power query application. The final section of the course contains other useful information on more advanced use, such as connecting to to doubt of boulders and using custom columns and using power query to automate procedures. Using Merrick were to sort about data. I'm converting pivoted data into table former before you get stuck into section two off this course, make sure you've completed the set up a pair of crew which we're going to look up in the next lesson. I would suggest that you watch the section full on, then followed the details in the activities to replicate the steps taken in the tutorial to replicate the steps taken in the tutorials completely. Activities in Section two before you move onto section for this course will give you a solid working knowledge of para queried, but you might want to explore at a more experienced level. So at the end of this course, you're gonna find a list of useful resources, so let's get stuck in. 3. Getting set up with Power Query: If you are using Excel 2000 and 10 or Excel 2000 and 13 you much download and install para query from the Microsoft website on. I have a link to that in the summary for this particular lesson. Now the following versions for Windows are supported. Microsoft Office 2000 and 16 All versions. So Power Query is known in Microsoft Office 2000 and 16 as Get on Transformed data in Microsoft Office 2000 and 13. It is supported in all versions in Microsoft Office 2000 and 10. It's only supported in professional plus with software assurance. The boat, the 32 bit on the 64 bit Softwares are supported on. At the moment, para Curry is not actually compatible with Mac versions of Excel. If you go to the download page, you will be greeted with a scream like this on. Basically, you can check this system requirements, the installation instructions, that sort of stuff, and you can just hit download on when you hit Download, you'll be asked to select between the 32 bit or the 64 bit on. You can download the one that's relevant to your particular version of Excel on. Once you download it and install it, I'm going to show you now how to actually turn it on in Excel. So here we are in Excel on. What we want to do is turn on the para query ribbon. So if you go to file on, if you go to options and in your options, you need to go to Adan's On in Adan's Down Here Under Manage E C. Excel Adan's. If you change this to Com's Adan's on Select Go and Incomes Adan's, then you have a number off Adan's that's available on you should know find Para query for Excel there. So if you take that box on press okay, you'll see a new ribbon appears for a para query. So that's just going through this power query ribbon. First of all, we have the GATT external data so you can get data from the Web you get out of from files. You can get out from Excel file CSB files, XML text, and you can also import from a folder. You can get data from a database you can set up SQL queries you can import from Arkle from i B M from SQL Server on there's different types of databases that you can import from. You can also import from a tzar so you can import out of from zero SQL database from their marketplace. If you have any came from their marketplace from the HD Insight server from the blob storage on their table storage desserts are than a list of online services such as micro stop, soft exchange online dynamics. Crm SharePoint Online. You can import details from Facebook from salesforce on again from Salesforce that may have other sources. Such a SharePoint borrowed out of feeds you can import from Hadoop fire. So if you have fires that you've created, the Butte scraped, big data on you have far is that you want to import. This is a great function to use from active Directory Microsoft Exchange. You can also import directly from SAP. You can import files from A O. D. B C, or you can also write a blank query. You can then import data from an Excel table itself in the table should be converted into a table to import it into power. Query. After this, then we have the marriage on upend options for combining data. We have the show pain on that. Show this pain over here and you'll see this later on. When we're looking at Power Query and more detail. Lunch editor is the query editor and we look at this again in more detail later on, then we have a data source settings we have our options on. In our options, we can change some global options and we can change details about the current workbook on. We also then have the option under power. Query. This update option and Power query is continuously being updated by Microsoft so you can make sure that this girl who update is on on you'll always have the most recent version of power Query. We then have the search death data catalogue and you see, search doctor cock log comes appear on. This is how we can search data from either the Web or if you have a power bi IAC aimed and you have data saved and there you can search for data. So just say, population and it searches. True data catalogue on that brings back detailed that it's able to find, for example, from the World Bank from Wikipedia and you get an A sample off the data when you click over one of these. So there's a lot of publicly available data, most of which pulls from Wikipedia or pulls from the World Bank. There's three United States Census Bureau, so there's a lot of big data sets in here available if you wish to compare your data with open data sources that this is slightly different to getting data from the Web, because when you get data from the Web, you need to have an actual Web address. But in here you can search for data catalogs on be able to return back details from a particular data catalogue. And that's basically an overview off the power query window. So they were going to be doing in this course is we be importing data from different sources. We be marriage ing. We be a pending we beaten, looking at the adviser Till, and you'll also see what's going on in the show pain area. Make sure now you take some time and you download and you install power query on to turn the add on on on Excel before you progress any further with this course 4. Conclusion and whats next: Congratulations on completing this class. So what's next? Will? In the project section, you will find a file off data for you to download on this file of data is to be used in the second and in the third class, which will follow later on during the week. So far, what you have done is you have installed power query, and you've made yourself familiar with the power query ribbon. In the next class, we're going to look at a scenario off how pair a query can actually be used to use data from an internal data source and combine it with an external data source from the Internet to give your business or your organization valuable insights. Thank you very much for taking this class, and I hope that you'll take the time to leave a review on, then hop on into the next class.