Forum Discussion
How to replace some NULL values by text values from another column in the same rows
You can also use this formula
let
Source = Table.FromRows({
{"Levi's 501 jeans", "Apparel"},
{"Toyota Corolla", "Automotive"},
{"iPhone 12", "Electronics"},
{"Chevrolet Cruze", null},
{"iPhone 14", null}
}
),
Replaced = Table.ReplaceValue(Source,null,each _[Column1],(a,b,c)=>a??c,{"Column2"})
in
Replaced
Hello Omid,
Maybe I wasn't clear. Let me tell you what I need :ID Product Name Category Name Customer Name 1 Pepsi Cola Beverages Yannick 2 Honda Civic Automotive Oliver 3 Zara clothing Apparel Kurt 4 Rolex watches Accessories/Jewelry Joel 5 Bose headphones Electronics Isabelle 6 Levi's 501 jeans null Emerode 7 Toyota Corolla null Noah 8 Pepsi Cola null Albert 9 PlayStation 5 null Sisca 10 Honda Civic null Jessy 11 Starbucks Coffee null Metthew I need for each "null" value takes a valid and good category name.
How to do this?
For example :
Row 1 Pepsi Cola = Berevages. But for the same product at the Row 8 = null?
Row 2 Honda Civic = Automobile. But at the Row 7, Toyota Corolla = null?
Row 9, we know that the PlayStation 5 element is on the Electronics category, same as Bose headphones. But on this row, we see null.
I'm wondering how to make sure that each "null" value is replaced with the correct category of each product in the "null" row?
Use this formula, Source is your table
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Product Name"}, {{"Count", each Table.FillUp(Table.FillDown(_,{"Category Name"}),{"Category Name"})}}),
#"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"ID", "Category Name", "Customer Name"}, {"ID", "Category Name", "Customer Name"})
in
#"Expanded Count"
