How to Mimic "Fill Down" in a Data Cube

#1

Hi Dundas community,

One of the developers on the team has an Excel file source that is not within his control to adjust the formatting for. Column A is made up of merged cells 5 rows down where they have the plant name. Column B and to the right has data in each row. When consuming into a data cube, this shows up as the plant name in Column A row 1, but blanks for Column A rows 2-5. He wants to be able to “fill” the plant name down for rows 2-5, but we are unable to mimic the transformation that some of the other platforms have.

We have tried to use an offset placeholder in a calculated element to say if there is a value in column A, use it, else grab the result of the row above, but you can’t refer to the element you are defining…

One alternative is to define 4 separate calculated elements and build it up for each of the 5 rows one by one, but we wanted to see if there was a more elegant way.

Picture below outlining the situation. Thanks!

image
image.png1341×416 8.2 KB

#2

Our Excel data connector essentially treats Excel sources similarly to any other flat file source like a CSV - it parses each row of data cell by cell. as such, grouped and merged cells like that would indeed be treated as if data is in one cell and the rest are blanks. I don’t see any options beyond the ability to treated the first row as a header and to treat mixed value types as text/strings in the data connector, so it seems like your calculated element is the best solution for now.

#3

Hi Christopher,

You can also use the Python Analysis transform and use the pandas ffill function.
image image.

Here is a sample script that would perform this fill, you can modify it to include other columns:

import pandas as pd
return pd.DataFrame($colToFill$).ffill()

1 Like
#4

Thank you both, I will have the developer try it tomorrow.

#5

Nour,

The python worked great. Some adjustments had to be made (below). Had to include all columns and add a data conversion.

import pandas as nd
return nd.DataFrame({"Plant":$fill down plant$,
                    "Metric":$Metric$,
                    "Month":$Month$,
                    "Target":$Target$}).ffill()

At the beginning was returning “null” in the cells to be filled. We had to “push” data conversion before to “transform” the Plant column from String to String (200).