Autofill blanks in column with most recently encountered

[Anonymous]

Given a table as outlined below, is there a way without using a lookup table to autofill the "Code" column values into the empty rows.?

Table

 Code      Description

A	something
B	Another
	Another
C	Yet more
	Yet more
	Yet more
	Yet more
D	Final

So you end up with a table like

 Code       Description

A	something
B	Another
B	Another
C	Yet more
C	Yet more
C	Yet more
C	Yet more
D	Final

[Anonymous]

Hi mobcdi

You may solve this by using the "fill down" feature in PBI Desktop.

- Transform

- Fill

- Down

Greetings

Hp Pfister

[Anonymous]

I just tried that and the blank values are still there even after I "apply". Does it matter that the table source is a csv file?

[Anonymous]

no, it doesn't matter what kind of source you use.

maybe there are some blanks or whitespaces in your code column.

can you check that?

if so, you have to replace this values first and then you can fill down the right values.

[Anonymous]

Applied trim and clean functions to the column and tried the fill down but nothing filled. I also calculated the length and where a value is missing it returns a length of 0

I also moved a column with a consistent value so its next to the missing data and applied the fill down across both columns but no joy

[Anonymous]

ok....

So please try this:

Add a new column and use this formula:

Character.ToNumber([Code])

Do you get any numbers for the "blank" rows?

[Anonymous]

I get errors for every row when I apply the formula  Character.ToNumber([Code]) but if I use Value.FromText([Code]) I get null for the missing values. I can't share the code values that appear in the problem column but they are 6 digits long and represented as text. Could that be causing the fill down to fail?

[Anonymous]

ok. Given your example I thought that your Code only contains 1 char...

That's why my formula can't work.

What you need is to write a functions, which splits the code by every char and then to test the number of the char.

Or... do you have a sample which you could upload here?

[Anonymous]

If I split the code column by number of chars (position) and apply your formula to the column with the 1st char i get the error below for every "blank" value

DataFormat.Error: We couldn't convert to Number.
Details

[Anonymous]

.. I guess without a screenshot or an example file we are stucked.

[Anonymous]

"Code","Description","Type",
"AB1234","2","LEC",
"","2","LEC",
"BC3456","2","LEC",

The csv sample above doesn't fill down for me. Could you try ?

I'm using Version: 2.43.4647.541 64-bit (February 2017) of PowerBI desktop 

[Anonymous · Accepted solution]

got it

add a new custom column

with this formula

if [Code] ="" then null else[Code]
then fill down in the new column

delete the column "code"

rename the new column to "code"

let
    Source = Csv.Document(File.Contents("C:\Users\xxxxx\Desktop\csvimport.csv"),[Delimiter=",", Columns=4, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source),
    #"Added Custom" = Table.AddColumn(#"Promoted Headers", "Custom", each if [Code] ="" then null else[Code]),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Code"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Code"}})
in
    #"Renamed Columns"

Greetings

Hp Pfister

[Anonymous]

Could also use replace values to replace the empty cell with null within the column itself then fill down. 

Seems strange fill down doesn't work unless the cells are null though

Anyway thanks for the help working this out