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 SharePoint lists you want to import:
But that would create one query per file. You would then have to apply the transformations on each of them and append them to retrieve one consolidated table.
Then use a trick
To make this procedure much easier, you just select ONE (sample) file instead. This will create a query with 3 steps automatically generated:
The trick is to delete the last 2 steps, so that just the Source-step remains:
This will generate a convenient selection-table that you can simply filter for the desired tables. In my case, I apply a text-filter that will select all lists whose names start with “Import”. That way my upcoming desired lists will automatically be included as well:
Then click on the 2 arrows to expand the list columns and select the columns you want:
That way you will maintain the list/file-name with your data and have a dynamic table that will be future-proof as well (as long as you’ve applied the correct filter syntax).
But depending on the length and complexity of your function, determining which functions are used and manually write down the record can become a bit laborious and spoil the fun. Therefore, I’ve created a function that does that for you autoMagically:
For the record
Pass in the query text as the first parameter and you’ll get a text string for the record:
Function record for Expression.Evaluate
For the query
And if you additionally pass in the URL for your function code as well, the full Expression.Evaluate-statement will automatically be created for you:
Full query text for Expression.Evaluate
Be aware, that the results of these functions are text that you have to copy and paste into your function code. You cannot simply reference the query, as this would repeat the problem with #shared.
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:
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:
var x=”&x&”;var y=new RegExp(‘”&y&”‘,’g’);var b=x.match(y);document.write(b);
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:
Import the Excel file in Power BI Desktop, save and close the pbix-file
Open Azure Analysis service, open the Web Designer and create a new model where you import the pbix
Open that model with Visual Studio (this will actually create a download that holds the VS-file)
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.