Forum Discussion

hood2media's avatar
hood2media
Resolver II
3 years ago
Solved

power query | replace value in 1 column based on content in another column - 2

hi,
i have a situation where contents in 1 column should be in another column & vice versa.

 

for example, a column called 'Category' has "New Year Party" and another column called "Item"has "Collection". 

the correction i need through power query is for "Collection"to be in the Category-column & "New Year Party" to be in the "Item Column".

appreciate any help for this.

tks & krgds, -nik

 

Need Help
Show and Tell
  • m_dekorte's avatar
    m_dekorte
    3 years ago

    Hi hood2media 

     

    If there's just that specific instance you need to change around. so your data is like this

     

    You could transform that, retaining other record fields, like so for example

     

    Copy this into a new blank query.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJLLVeITE0sUghILCqpBAo45+fkpCaXZObnATkhqRUlhkqxOtFKRuhSGBpBao2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Category = _t, Item = _t, Other = _t]),
    Transform = Value.ReplaceType(
        Table.FromRecords( List.Transform( Table.ToRecords( Source ), each 
            if _[Category] = "New Year Party" and _[Item] = "Collection" then _ & [Category="Collection", Item="New Year Party"] else _ )
        ), Value.Type(Source)
    )
in
    Transform

     

    Ps. If this helps solve your query please mark this post as Solution, thanks!

  • AjithPrasath's avatar
    AjithPrasath
    3 years ago

    Hi, In that case , the replace value in power query editor will work

     

    Category= Table.ReplaceValue(#"Changed Type","Collection","NY Party",Replacer.ReplaceText,{"Category"})

    Item= = Table.ReplaceValue(#"Replaced Value","NY Party","Collection",Replacer.ReplaceText,{"Item"})

     

     

    Ps. If this helps solve your query please mark this post as Solution, thanks!

5 Replies

  • m_dekorte's avatar
    m_dekorte
    Resident Rockstar
    3 years ago

    Hi hood2media 

     

    If there's just that specific instance you need to change around. so your data is like this

     

    You could transform that, retaining other record fields, like so for example

     

    Copy this into a new blank query.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJLLVeITE0sUghILCqpBAo45+fkpCaXZObnATkhqRUlhkqxOtFKRuhSGBpBao2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Category = _t, Item = _t, Other = _t]),
    Transform = Value.ReplaceType(
        Table.FromRecords( List.Transform( Table.ToRecords( Source ), each 
            if _[Category] = "New Year Party" and _[Item] = "Collection" then _ & [Category="Collection", Item="New Year Party"] else _ )
        ), Value.Type(Source)
    )
in
    Transform

     

    Ps. If this helps solve your query please mark this post as Solution, thanks!

  • AjithPrasath's avatar
    AjithPrasath
    Resolver II
    3 years ago

    In Power Query editor, You can change the column names of the respective column names. For ex: change the column name of the "Item" to " Collection" and vice-versa.

    • hood2media's avatar
      hood2media
      Resolver II
      3 years ago

      thanks AjithPrasath.

      actually, not all items r in that condition. only the wrong ones (based on above mentioned condition) that need correction.

      krgds, -nik

       

  • AjithPrasath's avatar
    AjithPrasath
    Resolver II
    3 years ago

    Hi, In that case , the replace value in power query editor will work

     

    Category= Table.ReplaceValue(#"Changed Type","Collection","NY Party",Replacer.ReplaceText,{"Category"})

    Item= = Table.ReplaceValue(#"Replaced Value","NY Party","Collection",Replacer.ReplaceText,{"Item"})

     

     

    Ps. If this helps solve your query please mark this post as Solution, thanks!