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.

RecordCreated DateWork ID
A4/1/202012345
B4/15/202023456
C6/20/2020

34567

D5/10/202045678
E12/25/202056789

 

I'm trying to use query to conditionally change the "Created Date" based upon the value of "Work Type".  I'd like to craft a query such that If "Work ID = 23456" then replace "Created Date" with the "Created Date" for "Work ID 56789".  So when finished, record B would have a Created Date of 12/25/20.

 

I'm having trouble crafting anything that works using Table.ReplaceValue that also contains my conditional logic.  Is there a better way?

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

3 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    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.

     

     

     

  • Mertubo's avatar
    Mertubo
    New Member
    3 years ago

    there are not only good answers but especially good questions 😉