Forum Discussion
How to replace some NULL values by text values from another column in the same rows
Hello
In the case of two columns, one "Product Name" containing non-zero values and the other "Category Name" containing 70% of values named "null", example on 5 lines: "Product Name" line 1 = Levi's 501 jeans, "Category Name" line 1 = Apparel; "Product Name" line 2 = Toyota Corolla, "Category Name" line 2 = Automotive; "Product Name" line 3 = iPhone 12, "Category Name" line 3 = Electronics; "Product Name" line 4 = Chevrolet Cruze, "Category Name" line 4 = null; "Product Name" line 5 = iPhone 14, "Category Name" line 5 = null,
How do I replace all null values with the non-null values in the Product Name column in the same row as the null values in the Category Name column?
5 Replies
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
ReplacedHello 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"
Hi lcmbany
If I've understood correctly, you want to replace each null Category Name value with the Product Name value in the same row.
Here's an example:
let Source = #table( type table [Product Name = text, Category Name = text], { {"Levi's 501 jeans", "Apparel"}, {"Toyota Corolla", "Automotive"}, {"iPhone 12", "Electronics"}, {"Chevrolet Cruze", null}, {"iPhone 14", null} } ), #"Replace null Category Names" = Table.ReplaceValue( Source, null, (_) as text => [Product Name], Replacer.ReplaceValue, {"Category Name"} ) in #"Replace null Category Names"Does this work as expected?
Hi OwenAuger
Sorry for my silent, was very busy at work!
I tried your code, but got result error.
Same as my firt answer to Omid_Motamedise, 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?
