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”

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:


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”

Filtering on multiple columns using one slicer

Power BI method for filtering on multiple columns with one field using only relationships and no DAX.

I’m rebuilding my main report in Power BI. This was the report I created when I first started learning to use Power BI so became quite a mess as I learned to do new things and added in new data sources. I’m rebuilding this from scratch so is a good time make some posts about the pitfalls I’m avoiding and some new techniques I’ve implemented.

The Request

The users want to be able to select a name from a slicer to view only information relating to that person’s sites.

The Challenge

I have a fact table that contains financial results by site. I have an estate map that gives further details for the site, including names of people who are related to that site. The Contract Director, the Regional Director, the Regional Manager, the Management Accountant, etc.

Each of these is a column so I need to be able to filter on any one of these columns using a single slicer. A person could also appear in multiple columns so I need to be able to show a site if the person’s name appears in any column.

Continue reading “Filtering on multiple columns using one slicer”