Forum Discussion

mikesdunbar's avatar
mikesdunbar
Helper I
1 year ago
Solved

Change column values based on different column values

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. Wha...
Need Help
  • OwenAuger's avatar
    OwenAuger
    1 year ago

    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's avatar
    v-ssriganesh
    1 year ago

    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.

ronrsnfld's avatar
ronrsnfld
Super User
1 year ago

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