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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
dylantaylor
New Member

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:

 

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

 

1 ACCEPTED SOLUTION
az38
Community Champion
Community Champion

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"

 

Снимок.PNG


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

4 REPLIES 4
az38
Community Champion
Community Champion

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"

 

Снимок.PNG


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
camargos88
Community Champion
Community Champion

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



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Anonymous
Not applicable

Yes, it is possible.

Best
D
affan
Solution Sage
Solution Sage

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors