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”
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 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”
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”
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”
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 users want to be able to select a name from a slicer to view only information relating to that person’s sites.
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”