The next thing we’re going to do is to add another column next to the index column. To do that, let’s first convert the list back into a table by clicking the To Table button in the upper left corner. Once we know where these dates are located, we can determine where each of our separate tables starts or ends. Now, we should identify which values on the list contain date values. Identifying The List For The New Tables For A Proper Tabular Format The first value of the list contains column 1, the second value contains column 2, and so on. The formula creates a list of the columns that we previously have. This creates a list of nested lists of column values from a table. We’re going to use the Table.ToColumns operation. Let’s click the fx button then add the formula that we’re going to use.

To do that, we’ll create the first formula for this tutorial. We have to break this table apart and stitch them back together into smaller tables. But since the tables are adjacent to one another, we can’t simply do that. To append these into one single table, we need to perform a basic unpivot operation to transform it into a proper tabular format. Columns 1 to 7 form one table, columns 8 to 13 form another table, and so on. Looking at the data that we now have, we see that there are four tables adjacent to each other. This should duplicate the dates and fill the gaps in the selected columns. Next, select all the columns that contain the dates, right-click the column header, then select Fill Down. The table should now look like it has been flipped on its side after performing the action. To fill these gaps, let’s first transpose our data table by going to the Transform tab then clicking the Transpose button. This happens not just on the top row but on the rows below as well. The dates are always followed by a null value as highlighted below.

Again, go to Remove Rows tab then click Remove Blank Rows.Īnother issue that we have is that the date values skip a column every time. Let’s also remove rows index 14 to 16 since they are completely filled with just null values. To do that, click on the Remove Rows tab, click Remove Top Rows, enter 2, then press OK.Īnd now to remove the first column, go to the Choose Columns tab, and then deselect column 1 by unchecking the box next to it. Let’s remove the first column and the top two rows of our data since we don’t really need them. The first step is to remove the unnecessary values in our data. If it’s not, go to the View tab then check the box beside the Formula Bar. But before we do that, let’s make sure that the Formula Bar is visible on your screen. To make things easier, let’s transform our data inside the Power Query Editor.

Grouping the Data For A Proper Tabular FormatĬleaning Up Data For A Proper Tabular Format.

Determining Which Values Belong In The Table.

Identifying The List For The New Tables For A Proper Tabular Format.

Cleaning Up Data For A Proper Tabular Format.

Power bi tabular editor download