How to fill blank cells based on the value of another column using Custom Column.

[ManojG]

Hi,

I have a table like this. I want to fill the blank cells based on values in other column. I have achived this through DAX and Fill option in Power Query.  Please help me in achieving this through creating Custom Column. I have Columns A, B, and C and i want custom Column like D. Thanks!

[ronrsnfld · Accepted solution]

And here's a different approach, adding the custom column within the Table.Group function:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0MjI2VtJRcgTi0GClWB0kMScsYs5AjBAxgep0DPZ0RBV1QhYF6TUxRbUFLuYEMxEu4owh4oLkEoj5LnB3QPhBQOzq6+qILBYMVhMLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CLM_ID = _t, Activity = _t, State = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CLM_ID", Int64.Type}, {"Activity", type text}, {"State", type text}}),
    
    #"Grouped Rows" = Table.Group(#"Changed Type", {"CLM_ID"}, {
        {"Expected Insured State", (t)=>
            Table.AddColumn(t,"Expected Insured State", 
                each List.Select(t[State], each Text.Length(Text.Trim(_))>0){0}, type text),
                type table[CLM_ID=Int64.Type, Activity=text, State=text, Expected Insured State=text]}}),
    
    #"Expanded Expected Insured State" = Table.ExpandTableColumn(#"Grouped Rows", "Expected Insured State",
         {"Activity", "State", "Expected Insured State"})
in
    #"Expanded Expected Insured State"

Source

Results

[ManojG]

Thank you so much @ronrsnfld, it works. much appreciated 🙂

[dufoq3]

Hi @ManojG, why via Custom Column?

Before

After

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0MjI2VtJRcgTi0GClWB0kMScsYs5AjBAxgep0DPZ0RBV1QhYF6TUxRbUFLuYEMxEu4owh4oLkEoj5LnB3QPhBQOzq6+qILBYMVhMLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CLM_ID = _t, Activity = _t, State = _t]),
    // This step can be deleted when applying qeury on real data.
    ReplaceBlankToNull = Table.TransformColumns(Source, {}, each if _ = "" then null else _),
    GroupedRows = Table.Group(ReplaceBlankToNull, {"CLM_ID"}, {{"All", each Table.FillUp(Table.FillDown(_, {"State"}), {"State"}), type table}}),
    CombinedAll = Table.Combine(GroupedRows[All])
in
    CombinedAll

v2 - Added new column:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0MjI2VtJRcgTi0GClWB0kMScsYs5AjBAxgep0DPZ0RBV1QhYF6TUxRbUFLuYEMxEu4owh4oLkEoj5LnB3QPhBQOzq6+qILBYMVhMLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CLM_ID = _t, Activity = _t, State = _t]),
    DuplicatedColumn = Table.DuplicateColumn(Source, "State", "Expected Insured State"),
    // This step can be deleted when applying qeury on real data.
    ReplaceBlankToNull = Table.TransformColumns(DuplicatedColumn, {}, each if _ = "" then null else _),
    GroupedRows = Table.Group(ReplaceBlankToNull, {"CLM_ID"}, {{"All", each Table.FillUp(Table.FillDown(_, {"Expected Insured State"}), {"Expected Insured State"}), type table}}),
    CombinedAll = Table.Combine(GroupedRows[All])
in
    CombinedAll