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

[hood2media]

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

[m_dekorte · Accepted solution]

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 · Accepted solution]

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!

[m_dekorte · Accepted solution]

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!

[hood2media]

many tks to both @m_dekorte & @AjithPrasath.

i'll chk your solutions & revert as necessary.

krgds, -nik

[AjithPrasath]

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]

thanks @AjithPrasath.

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

krgds, -nik