Referencing the first column in Power Query

Indirectly reference a column to stop your queries breaking

I recently had a problem where I was using the query builder in Power BI to extract data from all files in a folder. When some new files were added to the folder my query broke.

It turns out that the issue was with my promote headers step. Because the first file the query opened changed, the header that was promoted in the filename column was different, throwing off all my subsequent query steps. I needed a way of referencing the first column without referencing it by name. Continue reading “Referencing the first column in Power Query”

Five mistakes Excel users make when switching to Power BI

The great thing about Power BI is it makes creating advanced models and visuals simple for people like me who have come from a background of being an Excel Jockey. Here are some common pitfalls I’ve seen people make when first using Power BI. Continue reading “Five mistakes Excel users make when switching to Power BI”

Colour coded and parameter driven labels

One of the simplest but still one of my favourite Tableau tricks to add that little bit of extra insight into your visualisations is the ability to colour code your data labels.

Tableau doesn’t allow you to make certain labels go one colour and other labels another colour depending on a variable, so I’m going to show you how to get around it.

Note that this tip also used to apply to tooltips and can still be applied to them, but now you can embed worksheets into tooltips, that is an alternative way to achieve the same thing.

Continue reading “Colour coded and parameter driven labels”

Exporting larger datasets to Excel

Tableau’s ability to Export to Excel is really useful, and has saved me many hours down the years. This functionality is currently still lacking in PowerBi (sorry Jack but you know its true) compared to Tableau.

Quite often you will export your Tableau data to Excel to send somebody an extract of the data, maybe that person doesn’t have Tableau or the data is part of a much bigger piece of work and doesn’t warrant a whole Tableau Workbook getting sent. Sometimes these datasets will be large and can be quite a faff to sort out in Excel once it has exported. So here are a couple of tips to help reduce those woes.

Continue reading “Exporting larger datasets to Excel”

Public holidays in Power BI part 3 – Calculating the first and last Monday

In the previous parts of this series on public holidays we looked at calculating when Easter falls in the year and how to find the next Monday. Now we’ll be trying to find the first and last Monday of a month. Continue reading “Public holidays in Power BI part 3 – Calculating the first and last Monday”

Public holidays in Power BI part 2 – Calculating the next Monday

In part 1 we looked at calculating Easter Day. This time we’ll be looking at New Year’s, Christmas and Boxing day. If these fall on a weekend the public holiday will be the next available working day so we need a way to calculate the next working day. Continue reading “Public holidays in Power BI part 2 – Calculating the next Monday”

Public holidays in Power BI part 1 – Calculating Easter

Public holidays are hugely important when doing any time based analysis. For some businesses it might be a period where they close, for other businesses it could be peak trading.

When entering public holidays into Power BI you could just load in a table of dates from Excel. However this will require more maintenance. The ideal way to deal with public holidays is to have Power BI calculate them for you; then you can just leave the report to follow it’s refresh schedule. Continue reading “Public holidays in Power BI part 1 – Calculating Easter”