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

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:

Sample data
Sample data

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:

Sample Data to be loaded into Power BI
Sample Data to be loaded into Power BI

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
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
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.

Column, rows and cells in a Table in Power BI
Column, rows and cells in a Table

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:

Getting the first row of a Table
Getting the first row of a Table

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 in Power BI Desktop
Power Query expressions in Advanced Editor

But how do we concatenate the values of the rows?

Continue reading “Combining X Number of Rows in Power Query for Power BI, Excel and Power Query Online”

Integrating and Visualising Multiple Microsoft To Do Accounts with Power BI

Integrating and Visualising Multiple Microsoft To Do Accounts with Power BI

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.

Table of Contents

How It Works

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.

Continue reading “Integrating and Visualising Multiple Microsoft To Do Accounts with Power BI”

Quick Tips: Renaming All Tables’ Columns in One Go in Power Query

Renaming All Tables' Columns in One Go in Power Query

I previously wrote a blog post explaining how to rename all columns in a table in one go with Power Query. One of my visitors raised a question in the comments about the possibility to rename all columns from all tables in one go. Interestingly enough, one of my customers had a similar requirement. So I thought it is good to write a Quick Tip explaining how to meet the requirement.

The Problem

You are connecting to the data sources from Power BI Desktop (or Excel or Data Flows). The columns of the source tables are not user friendly, so you require to rename all columns. You already know how to rename all columns of a table in one go but you’d like to apply the renaming columns patterns to all tables.

The Solution

The solution is quite simple. We require to connect to the source, but we do not navigate to any tables straight away. In my case, my source table is an on-premises SQL Server. So I connect to the SQL Server instance using the Sql.Database(Server, DB) function in Power Query where the Server and the DB are query parameters. Read more about query parameters here. The results would like the following image:

The Results of Sql.Database() Function in Power Query
The results of running the Sql.Database(Server, DB) function

As you see in the above image, the results include Tables, Views and Functions. We are not interested in Functions therefore we just filter them out. The following image shows the results after applying the filter:

Filtering out SQL Server Functions After Connecting from Power Query
Filtering out SQL Server Functions

If we look closer to the Data column, we see that the column is indeed a Structured Column. The structured values of the Data column are Table values. If we click on a cell (not on the Table value of the cell), we can see the actual underlying data, as shown in the following image:

Continue reading “Quick Tips: Renaming All Tables’ Columns in One Go in Power Query”

Quick Tips: Conditionally Replace Values Based on Other Values in Power Query

Power Query (M) made a lot of data transformation activities much easier and value replacement is one of them. You can easily right click on any desired value in Power Query, either in Excel or Power BI, or other components of Power Platform in general, and simply replace that value with any desired alternative. Replacing values based on certain conditions however, may not seem that easy at first. I’ve seen a lot of Power Query (M) developers adding new columns to accomplish that. But adding a new column is not always a good idea, especially when you can do it in a simple single step in Power Query. In this post I show you a quick and easy way to that can help you handling many different value replacement scenarios.

Imagine you have a table like below and you have a requirement to replace the values column [B] with the values of column [C] if the [A] = [B].

Sample Data in Power BI

One way is to add a new conditional column and with the following logic:

if [B] = [A] then [C] else [B]

Well, it works perfectly fine, but wait, you’re adding a new column right? Wouldn’t it be better to handle the above simple scenario without adding a new column? If your answer is yes then continue reading.

Continue reading “Quick Tips: Conditionally Replace Values Based on Other Values in Power Query”