This is a quick walkthrough on how you can easily import multiple SharePoint lists at once, just like the import from folder method.
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:
If you use the M-function Number.Mod in Power BI or Power Query and expect the same result like in Excel or DAX, you are probably in good company.
But if the signs of the number and the divisor are not the same, M will differ from Excel and DAX:
Number.Mod in M is different
This is by design, so you can use this this formula instead, if you need matching results:
[Number] – [Divisor] * Number.RoundDown( [Number] / [Divisor] )
Enjoy & stay queryious đ
When you use Power Query as an Excel-automation-tool rather than just to feed the data model, you might want to return clickable hyperlinks that carry friendly names. This doesn’t work out of the box, but with a little tweak it will be fine:
Return a text-string that contains the Excel (!)-formula for hyperlinks, preceded by an apostrophe ‘ . After the data has been loaded to the sheet, check the column and replace ‘= by = to activate your Excel-formula:
Activate the HYPERLINK formula by replacing ‘= with =
You can then format the column to “Hyperlink”:
Today I’ll show you a very useful technique how to deal with a JSON record that contains a wild mixture of different elements like this:
If you click on one of the expandable elements, their content will be shown, but you’ll loose all the “surrounding” information (metadata) that is visible now. This is often an issue, regardless if you want to create multiple tables from it to build a star-schema or just need a handful of fields or a denormalized table. But with a little help from M, you’re good to go:
Table.FromRecords( { MyJsonRecord } )Will returns this:
With this move, every expansion of one of the expandable elements will keep the existing data in place:
Simply expand one element after each other to create a denormalized table
For multiple tables, keep this query and reference it to create you (sub-)tables. Always keep the Id-column as the key (!) to combine all the tables in your data model later. (Provided you use this in a function for multiple entities/series)
Best is to play with it, so just past this code into the advanced editor:
If your JSON-record has a different structure with “just” header and data in different fields, this technique will be more suitable for you: http://www.thebiccountant.com/2016/04/23/universal-json-opener-for-quandl/
Enjoy & stay queryious đ
Especially if you are new to R, there are some things one needs to know to successfully run R-scripts in the query editor of Power BI. I will share them here along with some tricks that made my R-life in Power BI easier:
If you click the icon âR scriptâ, the table from the previous step will automatically be passed as the âdatasetâ to the R-script. So if you donât fill in any R-code, this will happen:
But if you need the content from other tables as well, you just add them into the square brackets like this:
âDocumentationâ looks like this:
Apart from tables, you can also use text strings as parameters in the script. They need to be inserted into the code with preceeding â& and trailing &â:
RExportCsv= R.Execute(“write.csv(dataset,” “&CsvExportPath&” “)”,[dataset=Actuals])
Beware that they must be text. So if you want to pass a number, wrap it into Text.From(…).
Well, at least I havenât managed it 
The following function automatically transforms all columns to the types that have been detected in the cells of its first row. Provided they come as: Number, date or text (but you can add additional type conversions if you need them.).
It also has some rough edges: If the first value is empty, the column will be converted to text. Also, it contains the (improved) logic from this article: So if a date is written in a way that it could also be a number, then it will be converted as a number. To minimize the room for errors here, I’ve converted the values to text first, but this is still something to watch out for. But in very many cases it will just do what you have long been looking for:
Code to download: TableColumnTypesToFirstRowsTypes.txt
Enjoy & stay queryious đ