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"