Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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 |
Solved! Go to 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"
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 ,
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
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
User | Count |
---|---|
84 | |
81 | |
64 | |
53 | |
45 |
User | Count |
---|---|
100 | |
48 | |
41 | |
39 | |
38 |