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.

What even is an id?

The Power BI service is build up of many artefacts, some of which you are probably familiar with: reports, dashboards, workspaces, some of which you may not be: dataflows, capacities, gateways etc.
One of the things that all of these artefacts have in common is that the Power BI service uses globally unique identifiers, also known as a universally unique identifier, [GUID] to manage them.

Yesterday I spoke at the London Power BI User group about power query. If you want to view the recording it should be available on skills matter shortly.
You can also access the PowerPoint and Excel files on github.

If you attended the talk thank you so much for attending. If you have any questions you can either post them here or reach out on linkedin.

Have you ever had to update the background image on a report with a lot of pages? Perhaps due to a corporate rebrand, or maybe you no longer like yellow. Whatever the reason, changing the background image on a bunch of pages at once is no fun task. However, there is an easier way which I will go into but first some background info, pun intended.

A fact that I feel is often under appreciated is the fact that Power BI files are in fact zipped archive files. This means you can unzip these files just by renaming the file extension and using a tool like windows explorer or 7zip. I believe this is partly how tools like the the Power BI helper work, but that is just speculation on my part.

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.

When Microsoft enabled the ability for measures to have a data category such as image or web URL back in August of 2018. Quite a few people started using this to show dynamic images such as sparklines or KPIs. In this post I am going to built on these to create donut charts that can be embedded within Power BI Tables/Matrices using the same technique.

Measure 1: Five fixed values donut
This measure works by taking up to five specified measures and using them to build the donut chart.

Introduction

I think that the Power BI REST API is one of the most overlooked features of Power BI. It is an immensely powerful tool that can save you a lot of time when it comes to maintaining a large number of reports. Especially if you do not have access to some of the admin tools within your organisation.

While a REST API can look like an intimidating task it is far easier than you may think to get up and running with the API.

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.)

So I just took the 70-778 exam online, and I passed! I obviously can’t say what was on the exam but I will say a few thoughts I had about the preparation for and process of doing the exam itself.

In terms of preparation 80-90% of what prepared me was my experience with the Power BI. This isn’t a quick way to learn but it is the best and there is no substitute for it. I did buy the accopianing book by Daniil Maslyuk, which did help for some of the areas I wasn’t very experienced with. For example loading excel workbooks into the service or the administrative side of Power BI. The final piece of preparation that I found useful is a quiz that was made by Rishi Sapra. It has similar questions to what the exam contains. It is a bit out of date now, but it is still worth doing at least once.

This Power BI Report was created for the fourth London Power BI User Group [PUG] challenge. This is the second time I have done this challenge but the first I feel I have been pleased with my submission

If you are interested in Power BI I would really recommend going to a PUG. It is great to meet and learn from people who have a similar passion. If you come to the London PUG, take part in the challenges! They are every other month so you have two months to work on the challenge. They get announced at the user groups but also on Prathy’s blog.
The next London PUG is on October 23rd with Susie Bentley, hopefully I will see you there!