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