Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to conditionally update fields in one column, based upon data in another column.

I'm trying to find a way thru query to update a date field in one column, based upon the conditional value of another column.  Is there a way to do this?   Here is a simple example. Record Cre...
Need Help
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago

    Anonymous , Table.ReplaceValue, as well as Table.ReplaceText, is one of the most powerful function in M language which one could imagine; it does the trick for your request with ease. You might want to try

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PcxBDoAwCATAv3BuQsFS9ajVVxD+/w0B0952d5JVhQsKNCTkytUj8dYErCjcP8iUgJ4yvHWfpwTsKY83QVoScKS8+Y287kJOMPsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Record = _t, #"Created Date" = _t, #"Work ID" = _t]),

    Custom1 = Table.ReplaceValue(Source, "", each if [Work ID]="23456" then Source{[#"Work ID"="56789"]}[Created Date] else [Created Date], (x,y,z)=>z, {"Created Date"})
in
    Custom1

edhans's avatar
edhans
Community Champion
5 years ago

There is no basic code to do this Anonymous. It is possible but would involve much more complex code and may have performance considerations.

 

Even a simpler way may have performance issues. It depends on your data set. The best way is to have a new table that will do the cross reference for you. But if this is a one off, then this works. This is untennable with multiple date changes based on many different work IDs.

This

becomes this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PcxBDoAwCATAv3BuQsFS9ajVVxD+/w0B0952d5JVhQsKNCTkytUj8dYErCjcP8iUgJ4yvHWfpwTsKY83QVoScKS8+Y287kJOMPsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Record = _t, #"Created Date" = _t, #"Work ID" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Work ID", Int64.Type}, {"Created Date", type date}}),
    NewCreatedDate = 
        Table.AddColumn(
            #"Changed Type",
            "New Created Date",
            each if [Work ID] = 23456 then Table.SelectRows(Table.Buffer(#"Changed Type"), each [Work ID] = 56789)[Created Date]{0}
            else [Created Date],
            Date.Type
        ),
    #"Removed Other Columns" = Table.SelectColumns(NewCreatedDate,{"Record", "New Created Date", "Work ID"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"New Created Date", "Created Date"}})
in
    #"Renamed Columns"

 

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.