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:
Now we convert the list to a table by clicking the To Table button from the Transform tab:
Now we add a new column by clicking the Custom Column from the Add Column tab from the ribbon bar:
Now we use the following expression in the Custom Column window to pad the numbers with a leading zero:
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.
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.
Microsoft Excel is one of the most common data sources for Power BI. We can store Excel files in various storage types. The way we get data from Excel varies depending on the storage type. In this post, I quickly show two methods to connect to an Excel file stored in SharePoint Online.
Method 1: Getting the Excel File Path from the Excel Desktop App
This method requires you to have the Excel application installed on your machine. In this method, we open the Excel files stored in SharePoint Online in the Excel Desktop App in our machine and get the file path from there.
In SharePoint Online go to the desired document library then follow these steps to make it work:
Select the Excel file
Click the Open button
Click Open in app
This opens the Excel file in the Excel Desktop application. In the Excel follow these steps:
Click the File menu
Click the Copy path button
So far we got the Excel file path. The step is to get data from the copied path in Power BI Desktop.
Open Power BI Desktop and follow these steps:
Click Get data
Paste the path we copied from Excel in the URL text box
I am involved with a Power BI development in the past few days. I got some data exported from various systems in different formats, including Excel, CSV and OData. The CSV files are data export dumps from an ERP system. Working with ERP systems can be very time consuming, especially when you don’t have access to the data model, and you get the data in raw format in CSV files. It is challenging, as in the ERP systems, the table names and column names are not user friendly at all, which makes sense. The ERP systems are being used in various environments for many different customers with different requirements. So if we can get our hands to the underlying data model, we see configuration tables keeping column names. Some of the columns are custom built to cover specific needs. The tables may have many columns that are not necessarily useful for analytical purposes. So it is quite critical to have a good understanding of the underlying entity model. Anyhow, I don’t want to go off-topic.
So, here is my scenario. I received about 10 files, including 15 tables. Some tables are quite small, so I didn’t bother. But some of them are really wide like having between 150 to 208 columns. Nice!
Looking at the column names, they cannot be more difficult to read than they are, and I have multiple tables like that. So I have to rename those columns to something more readable, more on this side of the story later.
I emailed back to my customer, asking for their help. Luckily they have a very nice data expert who also understands their ERP system as well as the underlying entity model. I emailed him all the current column names and asked if he can provide more user-friendly names. He replied me back with a mapping table in Excel. Here is an example to show the Column Names Mapping table:
I was quite happy with the mapping table. Now, the next step is to rename all columns is based on the mapping table. Ouch! I have almost 800 columns to rename. That is literally a pain in the neck, and it doesn’t sound quite right to burn the project time to rename 800 columns.
But wait, what about writing automating the rename process? Like writing a custom function to rename all columns at once? I recall I read an excellent blog post about renaming multiple columns in Power Query that Gilbert Quevauvilliers wrote in 2018. I definitely recommend looking at his blog post. So I must do something similar to what Gilbert did; creating a custom function that gets the original columns names and brings back the new names. Then I use the custom function in each table to rename the columns. Easy!