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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
ManojG
Frequent Visitor

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

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!

Screenshot 2024-08-09 191751.png

1 ACCEPTED SOLUTION
ronrsnfld
Super User
Super User

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

ronrsnfld_0-1723421649019.png

 

Results

ronrsnfld_1-1723421675842.png

 

 

View solution in original post

3 REPLIES 3
ronrsnfld
Super User
Super User

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

ronrsnfld_0-1723421649019.png

 

Results

ronrsnfld_1-1723421675842.png

 

 

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

dufoq3
Super User
Super User

Hi @ManojG, why via Custom Column?

 

Before

dufoq3_0-1723212467356.png

 

After

dufoq3_1-1723212480143.png

 

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:

 

dufoq3_0-1723212647335.png

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

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

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

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

Top Solution Authors