How to replace some NULL values by text values from another column in the same rows

[lcmbany]

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?

[Omid_Motamedise]

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

[lcmbany]

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?

[Omid_Motamedise]

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"

[OwenAuger]

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?

[lcmbany]

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?