Skip to content

Let's POWER Automate

From no-code to low-code

Menu
  • Expressions
  • Filters
  • General
  • Problems
  • Triggers
  • Application specific solutions
    • Excel
    • Forms
    • Planner
    • Outlook
    • SharePoint
    • Teams
  • Resources
Menu

Get previous value(s) of modified SharePoint column(s) in Power Automate

Posted on August 15, 2021November 28, 2021 by Tom

“Can I use Power Automate to send an email with the previous and new value in a SharePoint column? I’m interested only in the changed columns.”


This post is an extension to the previous post ‘Identify which SharePoint item columns were updated‘. The outcome of that flow is an array with the changed columns. Only the column internal names, nothing else. While it’s nice to know that a column was updated, it’s better to know also the actual values. And by values I mean not only the new value, but also the previous value.

The result of this post is an email similar to the alert sent on item update. But this one will include only the updated columns and skip the rest.

Note: the version history on the SharePoint list must be enabled.

Get the previous item version

To get the previous field values, you must get the whole previous version of the item. That’s one of the reasons you need the version history, without it there’d be nothing to get. If there’s an existing previous version, use a HTTP request to SharePoint to get it.

Method: GET

Uri:
/_api/web/lists/getByTitle('<listName>')/items(<itemID>)/Versions(<versionID>)

Note: everything inside < and > are placeholders which you must replace.

The only problematic placeholder is the <versionID>. You might be tempted to use the previous version number in the same way you did in the ‘Get changes…’ action. But that’s not the expected <versionID> parameter. That number, e.g. 15, is the VersionLabel. The actual VersionId, which is required for the HTTP request, is a much higher number.

But that’s not a problem as the right number should be already available among your dynamic contents. It’s one of the outputs from the ‘Get changes…’ action.

outputs('Get_changes_for_an_item_or_a_file_(properties_only)')?['body/SinceVersionId']

Knowing this you can build the whole HTTP request, e.g.

/_api/web/lists/getByTitle('PlaygroundList')/items(@{triggerOutputs()?['body/ID']})/Versions(@{outputs('Get_changes_for_an_item_or_a_file_(properties_only)')?['body/SinceVersionId']})
Power Automate SharePoint column previous value

The output of this HTTP request will be a JSON with the previous version of the item, including all previous column values.

Get values for modified columns

Once you have the previous item, you can extract the column values and store them in a variable. But you’re interested only in values of the modified columns, not all of them. That leads us back to the previous post.

The output of the previous post was an array with the modified columns – the columns you’re interested in. If you loop through that array, you can extract from the previous version only their values. As the first step, initialize an array variable, e.g. var_changesArray.

Then add the ‘Apply to each’ to loop through the modified columns, the output from ‘Select’.

In each loop it’ll take one of the modified columns and store it as the ‘Current item’ (=item() expression). Navigating through the version history JSON, you can then take only the value for this specific column. For the value in the previous version history, the output from the HTTP request:

outputs('Send_an_HTTP_request_to_SharePoint')?['body']?['d']?[item()]

And for the value in the current item:

triggerOutputs()?['body']?[item()]

Place these expressions in their own ‘Compose’ actions for later processing.

Power Automate SharePoint column previous value

Format the values

The last step is to fill the array variable in a readable way. I say ‘readable’ because you’re still dealing with complex SharePoint columns, e.g. lookup, choice, or people picker. These column types contain a whole object, much more information than you need.

Power Automate SharePoint column previous value

And as such they’ll need an extra expression to process them. By using the if(…) expression you should check if the column contains specific “type”, and if it does, take only the ‘Value’, not the whole object.

If the column contains string ‘FieldLookupValue’, it’s a lookup column and you want to take the value in ‘LookupValue’ property.

The same logic applies if it contains ‘FieldUserValue’, which defines a people picker column.

For the previous item version it would mean expression as the one below. Check if the column string contains one of the values, and if it does, select the ‘LookupValue’. Otherwise use the column as is.

if(contains(string(outputs('Compose')),'FieldLookupValue'),
  outputs('Compose')?['LookupValue'],
  if(contains(string(outputs('Compose')),'FieldUserValue'),
    outputs('Compose')?['LookupValue'],
    outputs('Compose')
  )
)

For the current item version it’s a bit different as it uses different properties for those columns. Lookup columns are identified by string ‘SPListExpandedReference’ and the value is stored in ‘Value’. People picker columns contain ‘SPListExpandedUser’ and it allows you to take any user property, e.g. ‘DisplayName’ or ‘Email’.

if(contains(string(outputs('Compose_2')),'SPListExpandedReference'),
  outputs('Compose_2')?['Value'],
  if(contains(string(outputs('Compose_2')),'SPListExpandedUser'),
    outputs('Compose_2')?['DisplayName'],
    outputs('Compose_2')
  )
)

Using both the expressions you can append a new entry in the variable.

Then just convert the array to a string with the join(…) expression using some separator, e.g. a new line or HTML new line (<br>), and send the overview in an email.

join(variables('var_changesArray'),'<br>')
Power Automate SharePoint column previous value

In this post I used a simple, text only email, but since you’re working with HTML, you can format it in any way you’d like by adding html tags in the ‘Append to array variable’ action, e.g. by using <b>[Current item]</b>:

Summary

When you check SharePoint item for modified columns, you might need also the column previous value, and Power Automate can give it to you. The whole process, including filtering the modified columns, has multiple steps.

Firstly, you must extract only the modified columns. Secondly, get the previous version of the item. And thirdly, get the actual value and format it. You must take into account the complex column types, as shown on the people picker, choice and lookup column, and display them in a readable way.

There’s an extension to this post in case your list contains multiple selection columns.


Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

22 thoughts on “Get previous value(s) of modified SharePoint column(s) in Power Automate”

  1. Sumanth Dundi says:
    November 19, 2021 at 5:09 am

    Hi, Thank you so much for the post. But I see an error as following.

    Unable to process template language expressions in action ‘Append_to_array_variable’ inputs at line ‘1’ and column ‘20268’: ‘The template language expression ‘if(contains(string(outputs(‘Compose_2′)),’SPListExpandedReference’),
    outputs(‘Compose_2’)?[‘Value’],
    if(contains(string(outputs(‘Compose_2′)),’SPListExpandedUser’),
    outputs(‘Compose_2’)?[‘DisplayName’],
    outputs(‘Compose_2′)
    )
    )’ cannot be evaluated because property ‘DisplayName’ cannot be selected. Array elements can only be selected using an integer index.

    Reply
    1. Tom says:
      November 28, 2021 at 6:12 pm

      Hello Sumanth,
      take a look on the new blog post: https://tomriha.com/get-previous-values-of-sp-multiple-selection-columns-in-power-automate/

      Reply
  2. dinesh says:
    November 26, 2021 at 8:09 am

    Hi ,

    i am facing issue with multichoice column other wise your works fine.
    so please help me how to deal with multi choice column.
    the below code i am using but not working.
    if(contains(string(outputs(‘Compose’)),’FieldLookupValue’),
    outputs(‘Compose’)?[‘LookupValue’],
    if(contains(string(outputs(‘Compose’)),’FieldUserValue’),
    outputs(‘Compose’)?[‘LookupValue’],
    if(contains(string(outputs(‘Compose’)),’Collection’),
    outputs(‘Compose’)?[‘results’],
    outputs(‘Compose’)
    )
    ))

    if(contains(string(outputs(‘Compose_2′)),’SPListExpandedReference’),
    outputs(‘Compose_2’)?[‘Value’],
    if(contains(string(outputs(‘Compose_2′)),’SPListExpandedUser’),
    outputs(‘Compose_2’)?[‘DisplayName’],
    if(contains(string(outputs(‘Compose_2′)),’SPListExpandedReference’),
    outputs(‘Compose_2’)?[0][‘Id’],
    outputs(‘Compose_2’)
    )
    ))

    Reply
    1. Tom says:
      November 28, 2021 at 6:13 pm

      Hello dinesh,
      take a look on the new blog post: https://tomriha.com/get-previous-values-of-sp-multiple-selection-columns-in-power-automate/

      Reply
  3. James says:
    January 31, 2022 at 5:59 pm

    Thanks for the post. Is there a way to show the Sharepoint DisplayName within the email instead of the StaticName?

    Reply
    1. Tom says:
      February 2, 2022 at 6:57 pm

      Hello James,
      it’s doable. The approach would be similar to this post: https://tomriha.com/how-to-display-date-in-your-local-language-with-power-automate/. You’d need a mapping table and instead of using the ‘Current item’ directly in the ‘Append to array variable’ you’d have to access the corresponding value from the mapping table with an expression variables(‘MappingTable’)?[item()].
      Note: MappingTable is the name of the variable

      Reply
  4. Partha says:
    February 1, 2022 at 12:51 pm

    Hi, I was able to create the flow successfully, however I have noticed that by default the modified column alway appears in the email. is there a way to exclude that in the final email?
    Much appreciated for the help!

    Reply
  5. Partha says:
    February 1, 2022 at 12:53 pm

    Status updated from Close to Closed
    FulfilmentPriority updated from Critical to VeryCritical
    DemandProbability updated from 80 to 800

    Modified updated from 2022-02-01T11:34:43Z to 2022-02-01T11:36:04Z ( *****this one****)

    Reply
    1. Tom says:
      February 2, 2022 at 6:58 pm

      Hello Partha,
      you can add a ‘Condition’ before the ‘Append to array variable’. If the ‘Current item’ is equal to ‘Modified’, do nothing. Otherwise append the value.

      Reply
  6. Kyle Webb says:
    February 5, 2022 at 10:06 pm

    I am having an error at the “Compose” section:

    Unable to process template language expressions in action ‘Compose’ inputs at line ‘0’ and column ‘0’: ‘The template language expression ‘outputs(‘Send_an_HTTP_request_to_SharePoint’)?[‘body’]?[‘d’]?[item()]’ cannot be evaluated because property ‘{ “Audit_x0020_Status”: “” }’ cannot be selected. Please see https://aka.ms/logicexpressions for usage details.’.

    My “Audit Status” column is a single-selection field, so according to your notes on your more recent blog this should continue to work for me.

    Reply
    1. Tom says:
      February 7, 2022 at 10:01 pm

      Hello Kyle,
      you probably didn’t switch the ‘Select’ action to the value only mode: https://tomriha.com/how-to-select-specific-values-from-an-array-in-power-automate/

      Reply
      1. Kyle Webb says:
        February 8, 2022 at 5:16 pm

        Tom,

        You would be correct. I was able to resolve that issue with your help! However, I am now running into another issue. I followed the steps throughout the rest of the article, but the data I am returning via the join(…) expression is not returning the original value. I only receive the new value in the email I setup at the end of the flow.

        I thought it would be due to the type of field it is, but it is the same field I am changing and I receive the new value, so I am not sure why I wouldn’t receive the old value as well.

        My flow:

        Send an HTTP Request – /_api/web/lists/getByTitle(‘Administrator Audit’)/items(@{triggerOutputs()?[‘body/ID’]})/Versions(@{outputs(‘Get_changes_for_an_item_or_a_file_(properties_only)’)?[‘body/SinceVersionId’]})

        Compose – outputs(‘Send_an_HTTP_request_to_SharePoint’)?[‘body’]?[‘d’]?[item()]

        Compose 2 – triggerOutputs()?[‘body’]?[item()]

        Append to array variable –

        1) Current item

        2) if(contains(string(outputs(‘Compose’)),’FieldLookupValue’),
        outputs(‘Compose’)?[‘LookupValue’],
        if(contains(string(outputs(‘Compose’)),’FieldUserValue’),
        outputs(‘Compose’)?[‘LookupValue’],
        outputs(‘Compose’)
        )
        )

        3) if(contains(string(outputs(‘Compose_2′)),’SPListExpandedReference’),
        outputs(‘Compose_2’)?[‘Value’],
        if(contains(string(outputs(‘Compose_2′)),’SPListExpandedUser’),
        outputs(‘Compose_2’)?[‘DisplayName’],
        outputs(‘Compose_2’)
        )
        )

        Reply
        1. Kyle Webb says:
          February 9, 2022 at 4:18 pm

          The main issue I notice is there are no inputs or outputs shown when looking at the flow for the “Compose” action. It just says “click to download inputs/outputs” with no data showing. The “Compose 2” action shows the new value in my column.

          Reply
          1. Tom says:
            February 9, 2022 at 5:38 pm

            Hello Kyle,
            if the ‘Compose’ doesn’t return anything, then there might be a problem with the HTTP request. I’d check the output of the ‘Send an HTTP request to SharePoint’ action if it contains a valid JSON with all the previous values, it should contain pairs of “columnInternalName”: “previousValue”.

  7. Kyle Webb says:
    February 9, 2022 at 7:26 pm

    It seems like it is only returning the new value for some reason. The call I: am making is this:

    /_api/web/lists/getByTitle(‘Administrator Audit’)/items(@{triggerOutputs()?[‘body/ID’]})/Versions(@{outputs(‘Get_changes_for_an_item_or_a_file_(properties_only)’)?[‘body/SinceVersionID’]})

    I can confirm the select action returns the correct fields modified:

    [
    “Audit_x0020_Status”,
    “Modified”
    ]

    Reply
    1. Tom says:
      February 13, 2022 at 6:37 pm

      Hello Kyle,
      the ‘Select’ action looks fine, now you should check the JSON output of the HTTP request, if it contains data for the fields “Audit_x0020_Status” and “Modified”.
      And just to confirm the main prerequisite, you must have the version history enabled on the SharePoint list.

      Reply
      1. Sam G says:
        March 22, 2022 at 7:06 pm

        Hello Tom,

        Thank you for this amazing article. It is exactly what I have been looking for. However, I seem to have the same issue as Kyle Webb, were the outputs of my compose action only says “Click to download”. The only difference is sometimes it works for my old value and new value, other times only the new value works, and other times the old value works. I can’t seem to pin point why some fields work and others do not. What would you suggest I do to troubleshoot further?

        Thanks.

        Reply
        1. Tom says:
          March 29, 2022 at 11:18 am

          Hello Sam,
          isn’t it empty because there’s no value in that field?

          Reply
        2. Sean T says:
          May 16, 2022 at 3:57 am

          Hi all,

          I’m having the same problem, where the previous values are not appearing but the new values are. I have versioning enabled and the previous values do appear in the HTTP request body ‘d’.
          If anyone has had a similar problem and been able to solve it, could you please let me know, I’d really appreciate it.
          Thanks,
          Sean

          Reply
  8. CJ says:
    April 4, 2022 at 3:51 pm

    Hi, great post! Do you have a post about using the changed values to update another list with the same columns (both lists have a mapped/matching unique ID assigned when the items are created – not the SharePoint ID). Thanks!

    Reply
    1. Tom says:
      April 6, 2022 at 10:21 pm

      Hello CJ,
      if your goal is to keep the two lists synchronised then you can just find the corresponding item in the second list (https://tomriha.com/how-to-lookup-data-in-another-sharepoint-list-in-power-automate/) and then update all the columns, it doesn’t really matter which columns were updated, it’ll update all of them to the current value.

      Reply
  9. Sean T says:
    May 16, 2022 at 3:58 am

    Hi all,

    I’m having the same problem, where the previous values are not appearing but the new values are. I have versioning enabled and the previous values do appear in the HTTP request body ‘d’.
    If anyone has had a similar problem and been able to solve it, could you please let me know, I’d really appreciate it.
    Thanks,
    Sean

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

NOW AVAILABLE:

The Ultimate Power Automate expressions cheat sheet
Spend your time thinking about what the flow should do, not how to do it!

Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

Hello and welcome!

My name is Tom and I'm a business process automation consultant and Microsoft MVP living in the Czech Republic. I’ve been working with Microsoft technologies for almost 10 years, currently using mainly Power Automate, SharePoint, Teams, and the other M365 tools.

I believe that everyone can automate part of their work with the Power Automate platform. You can achieve a lot by "clicking" the flows in the designer, but you can achieve much more if you add a bit of coding knowledge. And that's what this blog is about.

To make the step from no-code Power Automate flows to low-code flows: using basic coding knowledge to build more complex yet more efficient flows to automate more of your daily tasks.

  • How to create a new SharePoint list column with Power Automate flowMay 22, 2022
  • How to combine expressions in your Power Automate flowsMay 18, 2022
  • Import Planner tasks with checklists into various buckets (Power Automate)May 15, 2022
  • How to get notified when Planner task was reassigned (Power Automate)May 11, 2022
  • How to extract value from XML using Power Automate flowMay 8, 2022

Power Automate blogs worth visiting

Damien Bird
Dennis (Expiscornovus)
Paul Murana

© 2022 Let's POWER Automate | Powered by Superbs Personal Blog theme