PowerBI Archives - Page 6 of 11

Dataflows, Excel, Power BI, Power BI Desktop, Power BI Service, Power Query

Combining X Number of Rows in Power Query for Power BI, Excel and Power Query Online

A while back, I was working on a project involving getting data from Excel files. The Excel files contain the data in sheets and tables. Getting the data from the tables is easy. However, the data in the sheets have some commentaries on top of the sheet, then the column names and then the data itself. Something like below:

This approach is pretty consistent across many Excel files. The customer wants to have the commentary in the column names when the data is imported into Power BI. So the final result must look like this:

The business requirement though is to combine the first 3 rows of data and promote it as the column name.

The Challenge

Let’s connect the Excel file and look at the data in Power BI Desktop.

Connecting to sample data from Power BI Desktop

As you can see in the preceding image, Power BI, or more precisely, Power Query, sees the data in Table format. After we click the Transform Data button, this is what we get in Power Query Editor:

Connected to sample data from Power Query in Power BI Desktop

We all know that tables consist of Columns and Rows. The conjunction of a column and a row is a Cell. What we require to do is to concatenate the values of cells from the first three rows. We also have to use a Space character to separate the values of each cell from the others.

In Power Query, we can get each row of data in as a Record with the following syntax:

Table{RecordIndex}

In the above syntax, the Table can be the results of the previous transformation step, and the RecordIndex starts from 0. So to get the first row of the table in the preceding image, we use the following syntax:

#"Changed Type"{0}

Where the #"Changed Type" is the previous step. Here are the results of running the preceding expression:

So we can get the second and third rows with similar expressions. The following image shows the entire codes in the Advanced Editor:

Power Query expressions in Advanced Editor

But how do we concatenate the values of the rows?

Continue reading →

Business Intelligence - BI, Power BI, Power BI Desktop, Power BI Embedded Capacity, Power BI Premium Capacity, Power BI Service, Self-Service BI

Power BI 101, What is Power BI

Many people talk about Power BI, its benefits and common challenges, and many more want to learn Power BI, which is excellent indeed. But there are many misconceptions and misunderstandings amongst the people who think they know Power BI. In my opinion, it is a significant risk in using tools without knowing them, and using the technology is no different. The situation is even worse when people who must know the technology well don’t know it, but they think they do. These people are potential risks to the businesses that want to adopt Power BI as their primary analytical solution across the organisation. As a part of my day-to-day job, I communicate with many people interacting with Power BI. Amongst many knowledgeable users are some of those who confuse things pretty frequently, which indicates a lack of understanding of the basic concepts.
So I decided to write a series of Power BI 101 to explain the basics of the technology that we all love in simple language. Regardless of your usage of Power BI, I endeavour to help you know what to expect from Power BI. This is the first part of this series.

What is Power BI?

I do not frequently get the “What is Power BI” question from my customers, my website’s comments, or my students within the training courses. It is indeed a question that I often ask people. I usually ask the question to indicate people’s level of understanding on different occasions, such as when a friend wants to know more about Power BI, or in a job interview from a candidate who applied for a Power BI related role, or my students attending a training course. Depending on the context that I ask the question, the responses are often pretty different.

It is the general rule of thumb to know what a “thing” is before using it. The “What is X?” (and X is the name of a “thing”) is a broad question, so the answer is also broad. Therefore we usually need more digging to get a better understanding of the “thing”.

In our case, the “thing” is Power BI, so the question is “What is Power BI?”. And the answer is:

“Power BI is the Business Analytics platform part of a larger SaaS platform called Power Platform offering from Microsoft.”

Now, let’s dig a bit more with two more questions:

What is a Data Platform?
What is Saas?

Let’s quickly answer those questions.

Continue reading →

Azure Analysis Services, DAX, Power BI, Power BI Desktop, Power BI Desktop RS, Power Query, Quick Tips

Quick Tips: Adding Leading Zero to Integer Values (Padding) with DAX and Power Query

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:

Continue reading →

Azure Analysis Services, Data Visualisation, Dataflows, DAX, Excel, Power BI, Power BI Desktop, Power BI Desktop RS, Power BI Service, Power Query

The Story of my Book, “Expert Data Modeling with Power BI”

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.

Continue reading →

Exit mobile version

The Challenge

Share this:

What is Power BI?

Share this:

Adding a Leading Zero in Power Query

Share this:

Share this: