cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Continued Contributor
Continued Contributor

Re: Autofill blanks in column with most recently encountered

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

Greetings Hp Pfister
Power Query Buch online auf http://www.powerbi-pro.com

View solution in original post

12 REPLIES 12
Highlighted
Continued Contributor
Continued Contributor

Re: Autofill blanks in column with most recently encountered

Hi mobcdi

 

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

 

- Transform

- Fill

- Down

 

Greetings

Hp Pfister

Greetings Hp Pfister
Power Query Buch online auf http://www.powerbi-pro.com
Highlighted
Helper I
Helper I

Re: Autofill blanks in column with most recently encountered

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?

Highlighted
Continued Contributor
Continued Contributor

Re: Autofill blanks in column with most recently encountered

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.

Greetings Hp Pfister
Power Query Buch online auf http://www.powerbi-pro.com
Highlighted
Helper I
Helper I

Re: Autofill blanks in column with most recently encountered

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

Highlighted
Continued Contributor
Continued Contributor

Re: Autofill blanks in column with most recently encountered

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?

Greetings Hp Pfister
Power Query Buch online auf http://www.powerbi-pro.com
Highlighted
Helper I
Helper I

Re: Autofill blanks in column with most recently encountered

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?

Highlighted
Continued Contributor
Continued Contributor

Re: Autofill blanks in column with most recently encountered

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?

Greetings Hp Pfister
Power Query Buch online auf http://www.powerbi-pro.com
Highlighted
Helper I
Helper I

Re: Autofill blanks in column with most recently encountered

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

Highlighted
Continued Contributor
Continued Contributor

Re: Autofill blanks in column with most recently encountered

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

Greetings Hp Pfister
Power Query Buch online auf http://www.powerbi-pro.com

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors