Also, since the second value in Column1 does not contain a date, then the second value in Index2 should only contain a null value. Since the first value in Column1 contains a date, the first value in Index2 should contain its index number 0. If it is, I want the formula to return the index number if it is not, I don’t want it to return any value at all. The brackets will serve as the operator adding 0 in between these brackets means we get the first value from the list then we determine if it is a date. We’re going to use the IF function, and then reference Column1. To do that, we’re going to incorporate a formula in Index2, which returns the index value of Column1 if it contains a date. This column should help us determine which values on Column1 contain the date values. 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. Cleaning Up Data For A Proper Tabular Format With that being said, let’s transform the data into a proper table format where we end up with a column for dates, a column for the movements, and another column for the names. However, this type of layout is harder to analyze since there are many columns and rows to take into consideration.
This type of arrangement is familiar to most of us since this is the way we can easily understand the data. Here, we have a data layout wherein the days are set up horizontally, and the weeks are set up vertically. Before we start, let’s first examine the data we have.
0 kommentar(er)
