The good folks from SumProduct have published a challenge for Excel that I find worth doing in Power Query instead:
Stretching or compressing a forecast so that the proportion of the original series will be maintained:
This is not so easy mathematically (as the solution-blogpost explains in detail), so the code for the function isn’t short as well:
From time to time Huang Caiguang sends me some geeky M-code that turns out to be very useful (like this for example). Today it’s utilizing java script code for RegEx (regular expressions): The Web.Page function can execute JS code and you can pass strings from you M-environment to it with simple string concatenation. So escape … Read more
Edit: This feature has been deprecated since 1st March 2019 unfortunately: https://azure.microsoft.com/en-us/updates/azure-analysis-services-web-designer-to-be-discontinued/ – Microsoft is working on replacement of the functionality to move pbix-files to SSAS-instances, but no solution yet. In Visual Studio there is a wizard to migrate an Excel Power Pivot model to a SSAS model. But this will not bring over the M-queries … Read more
In the first post of this series I’ve explained how to simultaneously load and combine multiple (similar structured) tables from ONE PAGE. This post handles the other direction, how to combine tables from DIFFERENT PAGES into one (or to scrape multiple webpages at once). Overview Theoretically, this is quite a simple process: Create a query that fetches and transforms … Read more
Although you can easily replicate the DAX SWITCH-function via list-, table- or record functions in M, I thought it would be convenient for many newbies to have a comfortable M-SWITCH-function that uses (almost) the same syntax than its DAX-equivalent:
SWITCH (
[Month],
1, “January”,
2, “February”,
3, “March”,
4, “April”,
5, “May”,
6, “June”,
7, “July”,
8, “August”,
9, “September”,
10, “October”,
11, “November”,
12, “December”,
“Unknown month number”
)
The DAX-SWITCH-function will retrieve the content of its first argument (expression) ([Month]) and check it against he first parameters of the following pairs (value). If there is a match, the second parameter of the pairs (result, here: month name) is returned and if there is no match, “Unknown month number” will be returned.
The syntax for the M-function looks like so:
This is a step-by-step description of how to combine multiple tables from one webpage into one table with categories as output. You can also apply this technique to combine tables from other sources as well (like from folder method for example or multiple different webpages (see this article)).
Sometimes the page you want to scrape has multiple tables like here:
And you want to combine them into 1 with a Category-column like so:
I will present 2 methods here:
You will also find 2 options at the end of this article: