Custom date formats in Power BI

Until the Power BI team gives us the ability to specify a custom date format on a column, one has to jump through a few hoops to get a custom date format to be recognized as a an actual date format. I finally decided to blog about it as it seems a common enough issue with a solution that is as simple as it is convoluted. Here goes.


As you can see, Power BI has failed to recognize this data as a date field and has decided to go for a numerical field. However, if we just try to change the format to a date, we are rewarded with this instead:

Not very helpful, I’m afraid. Clicking on the error message brings up a dialog that says that Power BI could not convert the field to a date – as there are just a few very select date formats that Power BI actually recognizes.

In order to get the original data (20180522 in this specific case) into a format that Power BI *will* recognize, one way is to do the following.

First, let’s split the column into the individual date parts. This can be done using “split column”, located on the Transform tab:

Unfortunately we can’t do the whole thing in one go, so we’ll do it in a few steps. The first step is to split the column by number of characters, like this:

This will give us one column with the four leftmost characters (2018) and one column with the rest. See where this is going? Right, but here is where it gets a tad evil.

You see, Power BI has already decided to automatically done a conversion to a numerical data type. That’s all fine and dandy, but it will wreak havoc with the next step as that’ll be splitting the second column just like we did with the first one. As the month part (05 in this case) has a leading zero, if we let Power BI convert this to numeric, the zero will be gone and the split will produce the wrong output. This is what we want to have at this stage, note the ABC icons for strings instead of the 123 of numbers.

After doing the splitting jig again, we’re left with this:

…and Power BI went ahead and changed the data type to a numeric AGAIN! Remove the “changed type” step and crack on – time to combine the columns into a usable field. Select the three fields we want to combine, and look for the “Merge Columns” button on the “Add Column” tab.

Power BI expects a date to behave in a certain way – one of the acceptable behaviors is having a dash between the date parts. When merging the columns we can select a custom separator and get the friggin’ dash in there, like so:

We are now treated to a new and shiny column called “MeasureDate” and Power BI thinks it’s a string. That’s OK, we’ll set Power BI straight in a second.

So can we NOW kindly have Power BI accept this as a date? Why, yes, thank you for asking! Power BI can easily interpret the above as a date, and we’ll end up with something like this (note the calendar icon of a date):

Now all that is left is housekeeping by selecting the three columns that we no longer need, right-clicking and choosing “Remove Columns”.

A quick column reorder later, we’re finally back where we started – but this time with a date field instead of a numerical one.

When you get tired of this clunky approach to what shouldn’t be a problem from the beginning, check out Lukas Lötter’s elegant PowerQuery function approach to solving the same problem here:


Combining tables in Power BI and finding two(?) bugs

A student of mine had a question about combining data from multiple Excel sheets and doing calculations on the data. I realized I didn’t have a clear-cut answer so I decided to do some digging. The scenario looked like this:

I created three excel files, each with a column for name and one for value. The idea is to simulate sales persons, sales amounts and having one file per product. I called the first file ProductA, the second ProductB and the third ProductC.




Then it was time to combine the tables. I wanted the result to be a table of all the data for the other tables, but it should also include where the data came from – i.e name, sales amount and product. This can be done in several ways, and one is to add a step in the loading of the excel files to add a column with a static name:

and then appending them together, like this:




The trouble is that this is static. There had to be a better way of doing it, and after spending a few hours trying to find one I asked Reza Rad (@Rad_Reza,, one of the most knowledgeable (and kind!) Power BI people on the planet. He showed me the use of the #shared construct. #shared lists all the Power Query (M) functions like this:


This is very useful for very many other applications, but in this case the goal was to remove the need for static table names. The first rows contain the names of the tables we’ve been using, so filtering those rows and expanding the data might give us what we need:


This looks exactly what we were after! With just a slight tweak of the column names we’re good to go. Turns out there’s a slight problem. As soon as I click apply the whole thing breaks:


After some discussion with Reza we came to the conclusion that there is a bug in the Table.FirstN function that filters out the rows. Using Table.Range or Table.RemoveLastN works much better. I’ve logged a bug at the forums, so hopefully this will be taken care of in the future.

Here is the Power Query code that do what I just did:


Reza did his own blog post on this technique as well as shared another nifty tip: using #section. Take a look at Reza’s blog!


*edit* It seems there might be another bug here, as the data won’t show up back in PBI Desktop, despite everything looking as it should in the query editor. Stay tuned…

SQLHangout #38 and Microsoft TechDays

My life is … hectic, to say the least. I just finished a talk at Atea IT-arena in Karlstad last week, as well as a 60-minute recap of Micrsoft Ignite in Atlanta that I held yesterday for 40 people at AddSkills in Linköping. The crowd was very attentive and lots of good questions were raised. Hopefully even more Swedes will go to Ignite next year! I’m preparing for speaking at the Atea IT-arena i Norrköping in late November as well as teaching two courses in December: 10986 (Upgrading your administration skills to SQL Server 2016) and 10989A (Analyzing data with Power BI). Full speed ahead!

At 1800 CET On Monday the 17th of October I have the pleasure to join MVP Cathrine Wilhelmsen for a chat about career transitions in IT. I’ve spent close to 20 years deep under the hood of database systems and only recently crawled up and decided to tackle something new – data visualization and the cloud. Or is it really new? That’s what we’ll cover on Monday, so be sure to tune in and don’t hesitate to tweet us if you have any questions!

SQLHangout #38 live stream

In November it’s time for Microsoft TechDays in Stockholm. I’m happy to say that I’ve received a speaking slot where I will be talking about Azure SQL Database – the cloud awakens. Or, as attendees will find out – what REALLY happened on the Death Star and what led to the downfall of the Empire. Few people know the inner workings of these events, and even fewer realized that it has a lot to do with IT…

SQL Database: the Cloud Awakens


Reducing SSIS anxiety

Story time again. This tuesday I was to create a set of import packages in SQL Server Integration Services (SSIS) to automate data loading from a couple of my customers’ storage systems. Said data comes in the shape of several text files – some delimited, some fixed length, all of varying size and shape. Anyone who’s worked with SSIS know how “fun” it is to click-click-click your way through the incomprehensibly boring GUI – a GUI that does NOT lend itself to any efficient work at all. We had been struggling with this for a couple of days before summer already, but the data format has changed and it’s basically the same amount of work to just redo the whole thing again from scratch than to try to edit all the tiny details in all the gazillion boxes everywhere.

I can’t really say that I was looking forward to this work. Again. And I knew that if we made this proof-of-concept work, we’d have to do it several times over for each and every customer that wanted in on this idea. Ugh. Then something interesting happened.
This weekend I had the honour of presenting at SQL Saturday #536 in Gothenburg and I had the pleasure of meeting several amazing people. Among these was a Norwegian MVP named Cathrine Wilhelmsen, whose presentation “From accidental to efficient BI developer” I decided on a whim to attend. She showed some interesting things about the SQL Server Management Studio that I had no idea about, and I felt that those tidbits alone were well worth my time listening to someone talk about something I know basically nothing about.

Then she whipped out BIML and everything I thougth I knew about SSIS went out the window. BIML stands for Business Intelligence Markup Language and is basically a XML wrapper language for generating SSIS packages. This way I can write a bunch of XML code, generate a package or a hundred, over and over again. I don’t have to click on forty different places to change one thing in forty packages, I just change one thing in the XML and regenerate the packages. This. Is. Awesome. She showed us some websites and her blog, and home I went to study.

Tuesday came around, and I first spent about 20 minutes outlining BIML for my colleague, and then we set off – basically from scratch. We are both old hands at programming in different languages, so that was nothing new for either of us. Using different websites, examples, blogs and the like we managed to do in about six hours what we had kind of failed to do in three days. We now have a few hundred lines of code that does exactly what we want, is well documented(!), is easy to read and above all – is extensible. When I need to add a new file or two to the input – I copy and paste some code. Need to add a derived column? Sure thing, a tad more XML. And so on and so forth. We’ve barely scratched the surface of what BIML can do, but the key takeaway is this: I could get from zero knowledge of BIML and a VERY difficult relationshop to SSIS to a fair grasp of the basics of BIML and a newfound appreciation of the power of SSIS in about six hours. When was the last time you had such a return of invested time? Exactly.
Now, BIML is definately not new but as a DBA I had not come across it earlier. Now I will sing the praise of BIML to all my DBA colleagues.

Now – I would not have attended Cathrine’s session had i not been invited to SQL Saturday in Gothenburg. I would not have been invited to Gothenburg had I not written an abstract and sent it for review to the organizers of said SQL Saturday. I had not had the idea to send in an abstract had I not met representatives of PASS (the association for SQL Server and BI professionals) at PASS Summit in Seattle and seen first-hand what the community can do. I’ve only been a part of the PASS community for a couple of years, but I can’t praise it enough. The community is about sharing above all – knowledge, contacts, the works. The people I’ve met have all been friendly, caring, eager to help and very, very good. I rate my getting involved in the community as one of the best and most pivotal choices of my professional career. My one regret is that I’m a bit late to the party. But I’m bringing cookies and 20 years’ worth of experience…

Useful links: