Forum, Tips & Tricks

Learn tips and tricks and engage in discussions with our blog authors here. It’s all for your benefit and learning. 

You need to log in to create posts and topics.

Using fill down in order to create structure from data (e.g. creating PnL with levels) #M #FillDown

I recently got some feisty-looking data that were promising in a way that they could be turned into a more structured table with levels and data. However, all the headings and whatnot were in the same column making the structure difficult to work with. For the sake of illustration, the data looked like this:

Picture 1: illustrative data

Due to the way Power BI works, the data in the first column need to be distinguished. In this case, there are 2 solutions to the problem. The sub-sections had a specific denotation which helped with creating conditional columns. Otherwise the ifs could be very tricky.

Solution 1: using the nulls in the column with values

This solution couldn't be used in my case since the column with values had also subtotals for some reason but if the data has this structure, this solution is a bit faster and easier. Basically you need to find something specific in the data that can be used to differentiate between the headings and subheadings (sections). In this case, we can see that there are no values in the rows with headings. Therefore we create an additional column that returns the heading if the value is null, otherwise it stays null.

Picture 2: conditional column (please note the "value" should have been in brackets)

Then we use the fill down function in Transform > Fill > Down, which duplicates the value on top to the next non-blank row.

Picture 3: Fill Down

Now we have the data ready, all that remains is to filter out nulls from the value column and do some switcheroos with the columns so that we have a nice and clean structure. What you end up with is some fine-lookin' data and a good warm feeling inside your heart.

Picture 4: Original data - restructured


Solution 2: finding other ways to restructure data

This solution is very similar to the first one, it's again about finding some specific symbols or differences so that you can create additional columns with a reasonable amount of if functions. In my case, there was no value column and the data had to be used to differentiate organizational levels within the company. I did notice that all the subsections started with "1-", which was then used in the conditional column.

Picture 5: Differentiating between the levels in the data

The same step has been done the other way around to create one more column with the headings only.

if Text.Start([org],2) = "1-" then null else [org]

Then we use the fill down function and this rollercoaster of emotions is closing to its end.

Picture 6: Creating additional columns, filling down the [org_lvl1] column - final touches before filtering out nulls from [org_lvl2] and deleting the first column ([org])

Picture 7: Filter out nulls, get rid of all the redundant columns and clean up your data

Zip zap zippity zap, now the data is in top shape, single and ready to mingle. Create some relationships with your existing data (I'd recommend having the organizational structure in a separate table that is linked to the values) and use that in a table or matrix to create a beautifully structured PnL or similar.

Add a matrix, put all the levels into the rows, use the double down arrows, i.e. expand all down one level in the hiearchy. I'd recommend going into the "row headers" setting and using the stepped layout, add some +/- icons into the mix so you can expand the levels all you want and need. Below you can see a couple of examples how the data with this structure can be used.

Picture 8: Few examples of the usage of structured data

Have fun, lads.