Fill in blanks based on previous records

[Anonymous]

I would like to fill the city column based on the zip column. See sample below

[v-jingzhang]

@Anonymous 

Select the columns with blank value, replace blank value with null, then select these columns again and use Fill Down.

Community Support Team _ Jing Zhang

If this post helps, please consider Accept it as the solution to help other members find it.

[FrankAT]

Hi @Anonymous 

you can do it very easy with Power Query like this:

// Table
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCihKLckvykxU0lFyTywtSc1LB7IMTY2AZHB+aUkGkHZMK8pMTlSK1YlWIk6VT2pBRmJOYk4qUNgnM7cgvyAfyDI1NcVpKD5p7C40MiZBtZExDpdiVW1sZEiK2YbYzI4FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [City = _t, State = _t, Zip = _t, Country = _t, #"(blank)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"City", type text}, {"State", type text}, {"Zip", Int64.Type}, {"Country", type text}, {"(blank)", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"City", "State"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"City", "State"})
in
    #"Filled Down"

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[Anonymous]

Hi @FrankAT 

do you mind sharing the file on which you have worked?

[Fowmy]

@Anonymous 

Not very clear about how you want to fill the blank, can you show the expected results. 

Power Query offers a function to Fill Down.

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous]

From the example, The second entry in 'city' is empty, however the previous record for the 'zip' 152, the city is Pretoria. Therefore I would like  a strategy to check if city is empty for certain zip, then the city should be the city for when that zip code was not blank. 

see desired result below;