September 20, 2019

London Power BI User Group September 2019

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.

May 31, 2019

Replacing background images in Power BI easily

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.

May 27, 2019

Reporting on Analysis Services metadata using Power BI

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.

The four DMVs that are useful for this are called TMSCHEMA_TABLES, TMSCHEMA_RELATIONSHIPS, TMSCHEMA_COLUMNS, and TMSCHEMA_MEASURES. Combined all of these let you see all tables, fields, relationships, and measures within the model. To demonstrate this I have built a Power BI Report that uses all of these to show information around an Adventure Works DW model.

May 26, 2019

How to get Donut Charts inside tables in Power BI using SVG

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.

April 9, 2019

Using PowerShell and the Power BI REST API

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.

January 19, 2019

How to get the most out of Web.BrowserContents()

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.

November 16, 2018

Query Folding with Salesforce

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.

November 14, 2018

Dealing with two level column headers in Power BI

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.

November 11, 2018

Using functions to create a filter in Power Query

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

October 21, 2018

Types and Folding within Power Query

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