Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mobcdi
Helper I
Helper I

Autofill blanks in column with most recently encountered

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

Asomething
BAnother
 Another
CYet more
 Yet more
 Yet more
 Yet more
DFinal

 

So you end up with a table like

 Code       Description

Asomething
BAnother
BAnother
CYet more
CYet more
CYet more
CYet more
DFinal
1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

12 REPLIES 12
Anonymous
Not applicable

Hi mobcdi

 

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

 

- Transform

- Fill

- Down

 

Greetings

Hp Pfister

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
Not applicable

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.

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
Not applicable

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?

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
Not applicable

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?

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
Not applicable

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

"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
Not applicable

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.