Forum Discussion
Auto Fill Blank Cells with Names
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 |
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"
4 Replies
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"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
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
