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”

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”

Dealing with multi-row headers in Power Query

Transform a crosstab format table with multi-row headers.

As Excel users we like to make some really odd looking tables. They make perfect sense to us when we look at them but, when you try and use them as a data source, Power BI struggles to understand them.

At work I recently needed to use an Excel workbook that had a table of data in crosstab format that had multiple header rows. I’m sure you’ve seen tables like it before:

Double-header

Usually on data like this you’d use the unpivot command to get all of your values into one column but having multiple headers makes this a bit more tricky. Continue reading “Dealing with multi-row headers in Power Query”

Power Query M and case sensitivity

In my last post (Filtering on multiple columns using one slicer) one of the steps I took was to remove duplicate entries from a list. The M language that Power Query and Power BI use is case sensitive. This means if you try and remove duplicates from a list and you have the same text but with different capitalisation, it will keep both of these entries when you try and use remove duplicates. This can cause some issues so lets look at how to fix it. Continue reading “Power Query M and case sensitivity”