Forum Discussion

demonfc's avatar
demonfc
Microsoft Employee
7 years ago
Solved

Dynamically renaming values

I have a list of information,  that is constantly updated, that needs values in a column to be changed to represent a different,  more generic value.    I would need to change each unique value in ...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    demonfc

     

    This is a third alternative solution. I think this is better than previous 2 approaches

    Using Power Query's Grouping Feature

    See Table2's Query Editor in attached file for the steps

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFNQ0lEKSC0qzs9TMAQyTSyUYnWilZwyc3IQEkZApqEZWMIlvzQdIWEMZBpBJVKLUrMRMiYgLcZgGefEIiSzTEESJmAJLNYb4ZIww2UUAWdhesTICKd7cZkFdS/uQMFiliVOLxri8IoxTsOMcUUKNLaiSpMSM4uAfBDCKgDUmpgD48cCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Data = _t, #"Expected Outcome" = _t, Score = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Data"}, {{"All Rows", each _, type table}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1),
    #"Added Prefix" = Table.TransformColumns(#"Added Index", {{"Index", each "Person " & Text.From(_, "en-US"), type text}}),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Added Prefix", "All Rows", {"Expected Outcome", "Score"}, {"Expected Outcome", "Score"})
in
    #"Expanded All Rows"
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
7 years ago

demonfc

 

This should do it

Please see the attached file's query editor to follow the steps

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFNQ0lEKSC0qzs9TMAQyTSyUYnWilZwyc3IQEkZApqEZWMIlvzQdIWEMZBpBJVKLUrMRMiYgLcZgGefEIiSzTEESJmAJLNYb4ZIww2UUAWdhesTICKd7cZkFdS/uQMFiliVOLxri8IoxTsOMcUUKNLaiSpMSM4uAfBDCKgDUmpgD48cCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Data = _t, #"Expected Outcome" = _t, Score = _t]),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Expected Outcome", "Score"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
    #"Added Index" = Table.AddIndexColumn(#"Removed Duplicates", "Index", 1, 1),
    #"Merged Queries" = Table.NestedJoin(Source,{"Data"},#"Added Index",{"Data"},"Added Index",JoinKind.LeftOuter),
    #"Expanded Added Index" = Table.ExpandTableColumn(#"Merged Queries", "Added Index", {"Index"}, {"Index"}),
    #"Added Prefix" = Table.TransformColumns(#"Expanded Added Index", {{"Index", each "Person " & Text.From(_, "en-US"), type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Added Prefix",{{"Index", "Person"}})
in
    #"Renamed Columns"

demonfc's avatar
demonfc
Microsoft Employee
7 years ago

Hi Zubair, 

 

Thank you. This seems to do the trick in practice. However, I am stumbling at the merge queries step. When i attempt the merge the source data with the new index table it is not an option available in power bi. Did you duplicate tables to allow the merge of the original query and the new table with the indexed table? 

 

Thank you

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    7 years ago

    demonfc

     

    This is one cool Power Query Trick :)

    Basically you can do a merge with one of the previous steps in the same query

    Unfortunately you cannot do the merge directly from the UserInterface.