Import data from multiple SharePoint lists at once in Power BI and Power Query

This is a quick walkthrough on how you can easily import multiple SharePoint lists at once, just like the import from folder method.

Start as usual

You start your import like this:

Pass the URL to the folder where your lists are located:

In the next step you would normally choose all the multiple SharePoint lists you want to import:

Read more

Permutations table in Power BI or Power Query in Excel

Today my husband came with a special task to me: He had to re-wire an old instrument on board of our sailing boat and “lost” the original matching instructions. There were 4 cables to go into 4 different holes, which left him with 24 possible combinations. So he needed a permutations table with all possible combinations that he could print out and tick each combination that he would try subsequently:

To be honest, he was quicker in creating the permutations table manually in Excel than me in Power Query, as it took me a while to develop an easy enough algorithm. But the technique I came out with at the end is so typical “Power Query”-ish, that I’m going to share it here:

The Video

The Function

Here comes the function code:

Read more

Stretching and Compressing Time Series with Power Query and Power BI

The good folks from SumProduct have published a challenge for Excel that I find worth doing in Power Query instead:

The Challenge

Stretching or compressing a forecast so that the proportion of the original series will be maintained:

The Function

This is not so easy mathematically (as the solution-blogpost explains in detail), so the code for the function isn’t short as well:

Read more

RegEx in Power BI and Power Query in Excel with Java Script

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 the js-code and use the ampersand (“&”) to reference the string. As a function it looks like so:

let
fx=(x,y)=>Web.Page(
“<script>
var x=”&x&”;var y=new RegExp(‘”&y&”‘,’g’);var b=x.match(y);document.write(b);
</script>”)[Data]{0}[Children]{0}[Children]{1}[Text]{0}
in fx(“””hello012中国1235″””,”\\d+”)

This code extracts all decimals (“\\d+”) from the string provided as the first argument and concatenates them with a comma. But just try for yourself by pasting the code into the advanced editor.

This doesn’t work in PowerBI service, so if you need it there, you can switch to an R-script that can be used with a personal gateway. But for Excel users, this is the way to go.

Please vote for a native implementation in M here: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/8892295-regular-expressions-support-in-power-query

Enjoy and stay queryious 😉

Migrate a Power Query or Power BI file to a local SSAS instance

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 unfortunately. But there is a workaround to achieve this. It requires SQL Server 2017 or higher:

The steps:

  1. Import the Excel file in Power BI Desktop, save and close the pbix-file
  2. Open Azure Analysis service, open the Web Designer and create a new model where you import the pbix
  3. Open that model with Visual Studio (this will actually create a download that holds the VS-file)
  4. Open that file in Visual Studio, load the data, build and change the deployment target from Azure to you local SSAS-database before deploying.

See how it goes:

Warning: There are some limitations for the M-functionalities in SSAS (see here for example: General Overview by Microsoft or Use your own SQL … by Chris Webb), so you might want to give it a thorough test before rolling out. There are missing a lot of data sources currently, like web-queries for example who will hopefully soon be added as well.

This method has been described by Soheil Bakhshi here before: http://biinsight.com/import-power-bi-desktop-model-to-ssas-tabular-2017-using-azure-analysis-services/ 

Enjoy & stay queryious 😉

A generic SWITCH-function for the query editor in Power BI and Power Query

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”
)
DAX Formatter by SQLBI

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.

How it works

The syntax for the M-function looks like so:

Read more