Change column values based on different column values

[mikesdunbar]

My fact table has two columns of interest, Customer and Industry (and others like Date, Revenue, they're not important for this). Some of the Customer entries have NA values, but not all of them. What I was trying to do was evaluate each row and if Industry has NA, then it will compare it to a customer and then change the NA to the appropriate industry.

Date	Customer	Industry
1/5/2025	A	Aerospace
1/10/2025	B	Baking
1/15/2025	C	Carpentry
1/20/2025	B	Baking
1/25/2025	B	NA
1/30/2025	D	Detailing
2/4/2025	A	NA

In the Above Customers A and B have their Industry, but also NAs.

My PowerQuery looks like this.

= Table.ReplaceValue(#"Changed Type1",
each [Industry],
each if [Customers] = "A" then "Aerospace"
else if [Customers] = "B" then "Baking"
else if [Customers] = "C" then "Carpentry"
else if [Customers] = "D" then "Detailing"
else [Industry],
Replacer.ReplaceText,{"Industry"})

It changes the A to Aerospace, but everthing else is the same. What am I missing here?

[OwenAuger · Accepted solution]

The structure/syntax of the step looks fine. Is it perhaps just a typo: [Customers] instead of [Customer]?

This test query appears to work as intended:

let
  Source = #table(
    type table [Date = date, Customer = text, Industry = text],
    {
      {#date(2025, 1, 5), "A", "Aerospace"},
      {#date(2025, 1, 10), "B", "Baking"},
      {#date(2025, 1, 15), "C", "Carpentry"},
      {#date(2025, 1, 20), "B", "Baking"},
      {#date(2025, 1, 25), "B", "NA"},
      {#date(2025, 1, 30), "D", "Detailing"},
      {#date(2025, 2, 4), "A", "NA"}
    }
  ),
  #"Replace Values" = Table.ReplaceValue(
    Source,
    each [Industry],
    each
      if [Customer] = "A" then "Aerospace"
      else if [Customer] = "B" then "Baking"
      else if [Customer] = "C" then "Carpentry"
      else if [Customer] = "D" then "Detailing"
      else [Industry],
    Replacer.ReplaceText,
    {"Industry"}
  )
in
  #"Replace Values"

[v-ssriganesh]

Hi @mikesdunbar,

May I ask if you have resolved this issue? If so, please mark it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-ssriganesh]

Hi @mikesdunbar ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[ronrsnfld]

Different approach using Table.ReplaceValue that can also account for multiple Industries being assigned to a given customer

• If Industry = NA
  • Make a Distinct List of all the non-NA Industries for that Customer
  • Concatenate the Industries (if there is more than one) to replace the NA
  • Note that this could be rewritten to replace NA with a single industry determined by some algorithm (first, most recent, etc)

Data

Paste code into Advanced Editor of a Blank query to replace what is there.

let

//Your original Table
//Replace the Source line with your actual data source reference
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31TcyMDJV0lFyBOHUovzigsTkVKVYHZCkoQFM1gmEE7Mz89JhUnCNziCcWFSQmldSVAmVNcKt0cgUWcrPESpsDNfhAsKpJYmZOTBNRvomyK4E6YkFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Customer = _t, Industry = _t]),
    
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Customer", type text}, {"Industry", type text}}),

//Replace NAs
    #"Replace NA" = Table.ReplaceValue(
        #"Changed Type",
        each [Industry],
        each [Customer],
        (x,y,z) as text=>if y="NA" then [a=Table.SelectRows(#"Changed Type", 
                                    each [Customer]=z and [Industry]<>"NA"),
                                 b=List.Distinct(a[Industry]),
                                 c=Text.Combine(b,",")][c] 
                    else x,
        {"Industry"}
    )
in
    #"Replace NA"

 Results

[SundarRaj]

Hi @mikesdunbar , I think instead of Table.ReplaceValue, you can use Table.TransformRows which will fit in perfectly in this siutation. I'll leave the images of the output and code below. Don't hesitate to ping back in case of queries. Thanks !

Here's the code:
let
Source = Excel.CurrentWorkbook(){[Name="Table9"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Customer", type text}, {"Industry", type text}}),
Cols = Table.SelectColumns( #"Changed Type",{ "Customer","Industry" } ),
TransformRows = Table.TransformRows( #"Changed Type", each _ & [ Industry = if [Industry] = "NA" then Record.ToList ( Record.SelectFields ( #"Changed Type" { List.PositionOf ( #"Changed Type"[Customer] , [Customer] ) } , "Industry" ) ) {0} else [Industry] ] ),
Custom1 = Table.FromRecords ( TransformRows )
in
Custom1

[v-ssriganesh · Accepted solution]

Hi @mikesdunbar,
Thank you for reaching out to the Microsoft Fabric Community Forum.

I have reproduced your scenario in Power BI using Power Query and was able to achieve the expected outcome as per your requirement. For your reference, I’m attaching a .pbix file so you can explore the complete steps directly.

Output:

Thanks you, @OwenAuger for sharing your valuable insights.

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[v-ssriganesh]

Hi @mikesdunbar,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please accept it as a solution and give it a 'Kudos' so other community members with similar problems can find a solution faster.
Thank you.

[OwenAuger · Accepted solution]

The structure/syntax of the step looks fine. Is it perhaps just a typo: [Customers] instead of [Customer]?

This test query appears to work as intended:

let
  Source = #table(
    type table [Date = date, Customer = text, Industry = text],
    {
      {#date(2025, 1, 5), "A", "Aerospace"},
      {#date(2025, 1, 10), "B", "Baking"},
      {#date(2025, 1, 15), "C", "Carpentry"},
      {#date(2025, 1, 20), "B", "Baking"},
      {#date(2025, 1, 25), "B", "NA"},
      {#date(2025, 1, 30), "D", "Detailing"},
      {#date(2025, 2, 4), "A", "NA"}
    }
  ),
  #"Replace Values" = Table.ReplaceValue(
    Source,
    each [Industry],
    each
      if [Customer] = "A" then "Aerospace"
      else if [Customer] = "B" then "Baking"
      else if [Customer] = "C" then "Carpentry"
      else if [Customer] = "D" then "Detailing"
      else [Industry],
    Replacer.ReplaceText,
    {"Industry"}
  )
in
  #"Replace Values"