Quick background first, what are a non-breaking spaces and why would you want to use them?

A non-breaking space is a character which looks the same as a normal space, however it prevents software that is rendering the text from inserting a linebreak where that space is. This is useful if you want to control exactly where text may be moved onto a new line, especially with different sized screens etc. The key point for Power BI and Power Query is that these non-breaking spaces have a different unicode value to the normal space.

Have you ever been looking for a function in the Power Query function documentation and been unable to find the function that you can see right there in the M code Power BI has generated for you?

I know I have especially with functions used to access data sources, well did you know there is a built in set of information right within Power Query! All you have to do is create a new blank query and paste this line of M code.

Analysis services has a series of what are called Data Management Views, which are essentially a way of seeing a bunch of meta data about your analysis services instance. With DMVs you can see a whole host of information such as activity, memory usage, active users, and more. However, to solve the issues I often come across the most useful DMVs are around the columns, measures, and tables that exist in the model. While you can query these DMVs through a tool like SSMS with SQL. Viewing these through Power BI can make the data exploration more interactive.

So back in my post about my submission to a London Power BI Challenge I mentioned how I had issues with the page I was trying to webscrape. This was because the links that I wanted didn’t load immediately but after a short delay. At the time I used a python script work around this. Now this did work for a one off report however due to Power BI lacking of support for refreshing Python code in the service this solution could not be used for a regularly occurring report.

Background

First some background to both Salesforce and Query Folding. Salesforce is a popular cloud enterprise software provider. Which, more importantly for this post, has a Power Query connector. It actually has two, the object and the report connector. However this only applies to the object connector. The object connector allows connection to the underlying tables that sit behind salesforce. Querying salesforce is done with a SQL-like language called Salesforce Object Query Language [SOQL]. You can see easily with the Salesforce Workbench.

Most of the time tables only have one header, however when there are more than one it can be a challenge to transform it into a useful form.

For this example I’ve created a table that contains made up actuals and budgets broken out by date. This is not an easy format to work with, we need to get it into a tidy unpivoted format that is easy to analyze.

The function Table.SelectRows is a very common power query function that is used to filter a table. It is what is created when you filter rows in a table using the UI. It takes a table and then applies whatever filters are set in the second argument. The following code generates a basic filtered table.

let
    Table = Table.FromRecords(
        {  
            [Id = 1,  Name = "Amanada", Age = 45],
            [Id = 2, Name = "Bob", Age = 13],
            [Id = 3, Name = "Christina", Age = 37],
            [Id = 4, Name = "David", Age = 23]
        }
    ),
    Changed_Type = Table.TransformColumnTypes(Table,{{"Id", Int64.Type}, {"Age", Int64.Type}, {"Name", type text}}),
    Filtered_Rows = Table.SelectRows(Changed_Type, each [Age] > 20)
in
    Filtered_Rows

This appears to have changed since I posted this and now it will query fold when types are changed in either manner.

I recently found this trick to improve folded queries when changing data types. It allows you to do type changes of columns without breaking the query folding. You can see if a step has been folded by right clicking on the step, if “View Native Query” is an option then the step has been folded. (Note that the opposite is not necessarily true, some sources do support some query folding yet do not allow you to see/edit the native queries.)