Power Query Formula Language is the code behind the scene for all Power Query operations. Anything that we do in Get Data & Transform graphical user interface will be converted to a script in a language informally named M (or Power Query Formula Language). This formula Language is much more powerful than graphical interface. There are some functions available in M which is not available in GUI. In this lesson you will learn basics about this scripting language.
Data analysis and BI world starts from data extraction and transformation. Power Query is the data transformation engine of Power BI. Power Query comes as part of Excel 2016, or as an add-in for Excel 2013 and 2010. Power Query is also part of Power BI Desktop. In this module you will learn about all different version of Power Query, their similarities, and differences, as well as configuration and requirements of using them.
Power Query uses a graphical user interface to apply transformations on the dataset. However Power Query works with a functional scripting language behind the scenes. The key to learn Power Query is learning the functional language called M. There are many features in M that are not available in the Power Query GUI. In this module you will learn how to understand M, and how to write M scripts even from scratch. You will learn writing custom functions in M as well as many other useful features.
Power Query is a functional language. Knowing functions is your best helper when you work with a functional language. Fortunately Power Query both in Excel and Power BI can use shared keyword to reveal a document library of all functions. In this lesson you learn how to use #shared keyword.
Custom Functions improves the script. Custom function increases consistency of the code, and reduces redundancy. Power Query enables you to write your own custom code, and re-use it in your Power BI application as many times as you want. In this less you learn how to create a custom function through demos and examples.
Generators create lists, and leveraging EACH singleton function besides a generator enables us to loop through the list and do data transformation per each item in the list. This method is a good way of implementing loop structure in Power Query. You will learn through an example of custom function how generators and EACH function used.
A reliable solution in Power BI should have a robust error handling implemented. Fortunately Power Query has the structure for implementing error handling. In this lesson you will learn how easy is to implement error handling in an example custom function.
Using a Date Dimension in any BI solution is a must to do. Not only because users might want to do slice and dice data by different date categories such as year, half year, quarter, month, week, fiscal categories. But also because sometimes you need to do analysis based on public holidays. Because Power Query can fetch public holidays live from a web query so it is a great tool for building a date dimension. In this lesson you learn how Power Query used for generating a date dimension with public holiday fetched live.
This is real use case for Power Query through an example. In this example you will learn how custom functions can be used with parameters to expand a data transformation applied on a CSV file to all files under a folder. The solution implemented with this method can be refreshed and pick the new data set anytime a new file appears in the source folder. You will learn how to apply simple data transformation in Power Query, how to change the whole transformation set to a function utilizing a parameter, and how to search through files in a folder, and apply function on all files and combine the whole result set.