Forum Discussion

integrated's avatar
integrated
New Member
4 years ago
Solved

change a brand name within data

I am new to Power BI, and have a data table from my client that has the word "UNKNOWN" listed as the brand for several SKUs. I would like to replace the word "UNKNOWN" with the actual brand name. I found a way to do this but it is only allowing me to replace all the "UNKNOWN" listings with one of the brands I need to update. How can I change some of the "UNKNOWN" listings to one brand and others to a different brand and so forth?

Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUQr18/bzD/dTitWJVkpC4yej8VPQ+Klo/DQ0fjoaPwONn4nGz0LmxwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Data = _t]),
    ListOfBrands = {"B1","B2","B3"},
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Calculated Modulo" = Table.TransformColumns(#"Added Index", {{"Index", each Number.Mod(_, List.Count(ListOfBrands)), type number}}),
    Custom1 = Table.ReplaceValue(#"Calculated Modulo",each [Data],each ListOfBrands{[Index]},Replacer.ReplaceValue,{"Data"}),
    #"Removed Columns" = Table.RemoveColumns(Custom1,{"Index"})
in
    #"Removed Columns"

     

5 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    Post some sample data and also highlight on what basis UNKNOWN should be replaced with what brand.

  • integrated's avatar
    integrated
    New Member
    4 years ago

    I want to change one of these "unknown" to brand 1 and another to brand 2 and so on

    • Vijay_A_Verma's avatar
      Vijay_A_Verma
      Most Valuable Professional
      4 years ago

      See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUQr18/bzD/dTitWJVkpC4yej8VPQ+Klo/DQ0fjoaPwONn4nGz0LmxwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Data = _t]),
    ListOfBrands = {"B1","B2","B3"},
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Calculated Modulo" = Table.TransformColumns(#"Added Index", {{"Index", each Number.Mod(_, List.Count(ListOfBrands)), type number}}),
    Custom1 = Table.ReplaceValue(#"Calculated Modulo",each [Data],each ListOfBrands{[Index]},Replacer.ReplaceValue,{"Data"}),
    #"Removed Columns" = Table.RemoveColumns(Custom1,{"Index"})
in
    #"Removed Columns"

       

    • Vijay_A_Verma's avatar
      Vijay_A_Verma
      Most Valuable Professional
      4 years ago

      So, I presume you want to replace 1st Unknown with Brand1, 2nd with Brand2...If there are 10 Unknowns and 3 brands, then 4th Unknown will be replace with Brand1 and so on....
      Is this what you want? 

      • integrated's avatar
        integrated
        New Member
        4 years ago

        That sounds right. They are not currently ordered that way but I could modify it as needed.