Microsoft Power Query

Power Query is the best way to bring data from other sources such as PDF, Excel files, SharePoint, websites, and databases into Excel. In Power Query, steps are performed to clean and prepare the data and then load it to the Excel sheet, a PivotTable, or stored and used in the data model.

What was once difficult, time consuming and would often involve advanced formulas or macros, is now easy thanks to Power Query.

Power Query is possibly the single best feature to appear in Excel in the last 20 years. If you work with a lot of data, you simple must learn how to use Power Query.

Anyone who gets data from other sources to work with in Excel should be using Power Query.

Objectives

By attending this workshop, you will learn about:

• Importing and transforming data from external sources
• Merge queries
• Parameters
• Calculations in Power Query
• Creating reports

Content

• Importing and Transform Data from External Sources
  Power Query makes it easy to import sources from a variety of sources including CSV, text, PDF, the web or even folders on a shared drive.Once imported we can perform transformation steps to prepare the data for analysis. Although there is much more to Power Query, this is its heartbeat. Simple (and more complex) transformations all recorded to be refreshed at the click of a button in the future.
• Default Settings
  Adjust some of the default PQ settings to fine-tune it to your needs. Schedule refreshes to update queries automatically and change how it recognises data.
• Merge Queries
  Merge Queries are a fantastic feature of Power query. Making it easy and very efficient to combine data from different tables/sheets and to compare lists. We will explore some of the different join types to compare lists and to ‘lookup’ and merge columns from other tables.
• Parameters
  Learn how to use parameters in Power Query to dynamically change a value in your queries. These can help us to create dynamic and interactive reports, or provide a simple method to switch a data course or value of a custom function.
• Calculations in Power Query
  There are lots of built-in functions for performing statistical, standard, rounding and date calculations in Power Query, to name a few. There is also a good interface for creating conditional columns, the power of custom functions and the brilliant column from examples. We will explore and see example of some of these Power Query calculations.
• Creating Reports
  Power Query data is then loaded to a table on the worksheet, a PivotTable, connection only, and can be added to the data model for further analysis and reporting. We will explore each of these options.What is the data model? Let’s learn what this is how it can improve how we model and report data.