Auto Fill Blank Cells with Names

[dylantaylor]

Hello,

Is it possible to use the fill feature in Power Query Editor to fill blank cells with names? In this example, I am trying to fill "Wentworth Property Co" to only the blank cell below, stopping at "Estrella Mountain Ranch" below:

Number	Name
1	City of Freedom
2	Waste Management
3	Wentworth Property Co.
4
5	Estrella Mountain Ranch

[az38 · Accepted solution]

Hi @dylantaylor 

in your case try this workaround:

1. Trim column

2. Replace value "" to null

3. Fill Down column

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HYvLCsJADEV/JWRdBF9fUHTXIm5cDLOIGm2hk5Q0Rfx74+zOPYebEvZrubNhgz0Vxtwk3MZoR/+CvuBszE8t1e/C32hxho6E3lxYvIb9P8T4qPkAF9OZLe6tbmo+RIZKx6DT4sbTRNDpKk6jwJXkMWDOPw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Number", Int64.Type}, {"Name", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"Name", Text.Trim, type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Trimmed Text","",null,Replacer.ReplaceValue,{"Name"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Name"})
in
    #"Filled Down"

[camargos88]

Hi @dylantaylor ,

You can use fill down for it. Fill down gets the last non null value and fills till the last null value....for each occurency.

Ricardo

[Anonymous]

Yes, it is possible.

Best
D

[affan]

Hi @dylantaylor 

Not sure, but you can try using the create a column from examples in Query editor. Have a look on this https://docs.microsoft.com/en-us/power-bi/desktop-add-column-from-example

If this helped you, please mark this post as an accepted solution and like to give KUDOS .

Regards,

Affan Farooqi

LinkedIn