There are some cases that we want to add a leading zero to a digit, such as showing 01 instead of 1, 02 instead of 2 and so on. We have two options to do this in Power BI, doing it in Power Query or doing it with DAX.
Adding a Leading Zero in Power Query
The first method is doing it in Power Query using the Text.PadStart() function.
Here is how the syntax of the function:
Text.PadStart(text as nullable text, count as number, optional character as nullable text)
And here is how the function works:
Text.PadStart(input string, the length of the string, an optional character to be added to the beginning of the string util we reach to the string length)
For example, Text.PadStart("12345", 10 , "a") returns aaaaa12345 and Text.PadStart("1", 2 , "0") returns 01.
Let’s create a list of integer values between 1 to 20 with the following expression:
{1..20}
Creating a List of Integer Values Between 1 to 20 In Power Query
Now we convert the list to a table by clicking the To Table button from the Transform tab:
Converting a List to a Table in Power Query
Now we add a new column by clicking the Custom Column from the Add Column tab from the ribbon bar:
Adding a New Custom Column to a Table in Power Query
Now we use the following expression in the Custom Column window to pad the numbers with a leading zero:
Do you have an old report created in 2016? If you do, then you know that the visuals’ style was pretty different back then. If you don’t like to see all visuals in their old style where the headings overlap the other visuals and you don’t know how to switch the visuals to their modern style, then continue reading. The following screenshot is from a very old report I built for a Power BI demo in Feb 2016! Time flies mate!
Old Style Visuals in Power BI Desktop
If you are an old Power BI developer, you can clearly remember those days when we had many side-by-side slicers on the report canvas and sometimes it was hard to click a slicer because hovering over the visuals activates the visual frames and it is easy to mistakenly click a different visual.
The other issue with the old visual style is that the Visual Header options of the Visualisations pane from the Format tab are not available for traditional visuals.
Visual header options are not available for old-style visual
I see some developers don’t like the old visual style. It is interesting that when we work on an old report, even if we open the report in the latest version of Power BI Desktop, the visuals’ style remains the same and it doesn’t change if we add new visuals to a new page. Some developers go through a lot of pain to get the visuals to work in the modern style. I even know some developers copied all queries from the old file’s Power Query and pasted them to a new Power BI file. Then they used Tabular Editor to copy the tabular objects from the old report to the new one. And some even started to build everything from scratch. If you are one of those developers, be aware that there is a simple setting that can help to quickly switch all the old visuals to their new modern style.
In 2020, the world celebrated the new year with many uncertainties. Well, life is full of uncertainties, but, this one was very different. The world was facing a new pandemic that never experienced before. The first COVID19 case in New Zealand was confirmed in February 2020. In March 2020 the entire country went to lockdown for the first time. The world was experiencing a massive threat changing everyone’s lives. I was no different. Every day was starting with bad news. A relative passed away; a friend got the virus; the customers put the projects on hold etc. Nothing was looking normal anymore. You can’t even go to get a proper haircut, because everyone is in lockdown. This is me trying to smile after getting a homemade haircut. I bet many of you have done the same thing.
Soheil’s Homemade Haircut
One day, I checked my email and saw a message from Packt Publishing. They wanted to see if I am interested in writing a book about Power BI. That was a piece of good news after a long time. I always wanted to write a book about Power BI. Indeed, I attempted for the first time in 2016, but I couldn’t manage to get my ducks in a row to grasp the publishers’ attention.
I was not unfamiliar with writing books; indeed, I wrote my first book back in 2006 about Multimedia Applications in Persian. One of my passions in life is listening to music. And CDs were the most accessible music source with high-quality sound. I recall I saved money for some months, and I bought a Discman to listen to the music on the go. But CDs are rather bulky, and you could not have many of them in your pocket. So the next project was to save even more money to buy an MP3 player. But, converting Audio CDs to MP3 without compromising a lot on the sound quality was a real challenge for many people. And, that was my motive to write my first book in Persian to share my little knowledge with everyone.
It’s been a while that I use Microsoft To Do to organise my daily tasks. From work-related tasks to buy groceries. While Microsoft To Do is super easy to use but there are some challenges in using it more efficiently, especially when you have multiple O365 accounts within different organisations. Here are some of the challenges I faced; you may face other challenges too:
The Microsoft To Do app for Windows devices is very user friendly with amazingly good features like the ability to add multiple To Do accounts. However, we currently have to select which account we would like to use and the app shows all our tasks within that specific account. This means we can not see all our tasks from all our accounts in a single place.
The Microsoft To Do app for iOS devices is also very handy to use, but it lacks adding multiple accounts. Hence we cannot see all our tasks from multiple O365 accounts on the app. 🙁
We can use the Tasks within the Microsoft Outlook desktop application (I used the Windows version) which is by far the most comprehensive one with tons of features. While we can see tasks from multiple accounts in a single place, it is a real challenge if I want to know which task is assigned to which account. Besides, it is really hard to answer some questions like, how many high-priority tasks I have for today or the week ahead. I know, we can group tasks, but, it is still not so intuitive.
For the above reasons, I searched for a product that can do all the above at once. After spending some hours, I thought, well, I have to do it myself.
With that, let’s go ahead and see how we can get the job done in Power BI.
Note:
This method is not working for Microsoft To Do using personal accounts such as Outlook, Hotmail or MSN. If anyone knows how to add those, please let us know in the comments section below this post.
This is a long post that took me a reasonable amount of time to write. So I added the following table of contents so you can quickly jump to a subject of your interest.
Microsoft Power BI is NOT a reporting tool only. We can connect to many data sources, mix and match the data, create data models and visualise the data. So it should be possible to connect to multiple To Do accounts, append the data, create a simple data model on top of that, and visualise the data to answer our questions or our customers’ questions. The Microsoft To Do data is accessible via the Microsoft Exchange Online connector available in Power BI. The rest depends on our requirements and what questions we would like to answer.
In my case, in which I am the end-user of the report, I would like to be able to know:
Today’s tasks: All tasks that their StartDate or DueDate is today or the Tasks without any StartDate and DueDate
Number of tasks
Number of important tasks
Tasks by mailbox
Tasks details
Task list
Task description
Status
Start date
Due date
A link to the task itself that I can update if I want to
All Tasks
All above plus
Number of open tasks
Number of completed tasks
You or your customer(s) might have different requirements, but once you understand how to get the To Do data from Microsoft Exchange Online and do some data explorations to find out what you are after, you’ll be good.
