Swap values in the same row between two columns based on certain condition

Hi Hennadii ,

Not sure if this is what you need but I assume that for each line that the item_id is no present in the task you want to make the change.

What I did in the query editor was the following:

• Merge the relations table with the task table
  • Full Outer all row from both
• Expand the ID column
• Add a new custom column and name it item_to_id_swapped:

if [Tasks.id] = null then[item_id]else [Tasks.id] 

• Select a null value on the Tasks ID and replace the null by a value 999
• Now edit the just created step of the replacemente with the following code:

= Table.ReplaceValue(#"Added Custom",each [Tasks.id],each if [Tasks.id] = null then [item_to_id] else [Tasks.id] ,Replacer.ReplaceValue,{"Tasks.id"})

• Rename the Tasks.id column

Final result below and in attach PBIX file (december version).

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0ABKmhkqxOtFKRkCmEYhvBuEbQ6TAgiC+CZBpbADlxwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, item_id = _t, item_to_id = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"item_id", Int64.Type}, {"item_to_id", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"item_id"}, Tasks, {"id"}, "Tasks", JoinKind.FullOuter),
    #"Expanded Tasks" = Table.ExpandTableColumn(#"Merged Queries", "Tasks", {"id"}, {"Tasks.id"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Tasks", "item_to_id_swapped", each if [Tasks.id] = null then[item_id]else [Tasks.id] , Int64.Type),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom",each [Tasks.id],each if [Tasks.id] = null then [item_to_id] else [Tasks.id] ,Replacer.ReplaceValue,{"Tasks.id"}),
    #"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"Tasks.id", "item_id_swapped"}})
in
    #"Renamed Columns"