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 f...
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"

     

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.