Skip to main content

Notifications

Power Automate - General Discussion
Unanswered

Updating Excel using office 365

(1) ShareShare
ReportReport
Posted on by

Hello everyone,

 

I need some assistance and explanation from you experts. 

 

I have an Excel file that I've uploaded to OneDrive. Currently, only the email ID columns are filled in the Excel sheet. There are more columns present in the Excel file.

 

What I'm attempting to do is update the remaining fields in the Excel file using Office 365's "Get User Profile" feature. However, I'm not sure how to proceed from here. Additionally, I need to send an email to each user.

 

I've encountered a roadblock and would appreciate any guidance.

 

Danny_Dicaprio_1-1710327718239.png

 

Essentially, I need to use the email ID column in my Excel sheet to retrieve user profiles, update the corresponding fields in the Excel file, and also send an email to each user.

 

 

Thank you for your help and expertise.

 

Categories:
  • Joseph_Fadero Profile Picture
    Joseph_Fadero 666 on at
    Re: Updating Excel using office 365

    Hi @Danny_Dicaprio ,

    I trust you are good,

    Here is a follow up on my recommendation above,

    I would like to know if you have resolved the issue you had earlier,

    Kindly feel free to Like my response and mark as a solution if it aided in resolving your issue, to assist others with similar issues in the community

  • Joseph_Fadero Profile Picture
    Joseph_Fadero 666 on at
    Re: Updating Excel using office 365

    Hi @Danny_Dicaprio ,

    Oh I see, so to be honest the issue isn't from your excel, rather from your 365 database, Because the value can be mixed i.e it doesn't follow an actual format that maybe an issue. 

    My suggestion is, If it's Possible the usermail on the 365 database should be properly structured, If not, Can you get the email column from that 365 section, because you need that mail to actually establish a connection between your spreadsheet and your mail. You need to either retrieve the UserID or the Usermail from the actual employee database as it is, for your flow to work effectively, If it was a case that all of them follows the same format, then an expression would have easily worked

  • Re: Updating Excel using office 365

    @Joseph_Fadero   Thanks for responding

     

    The problem is in excel its lower email, But

     

    when we use Get user profile,  for some user its lower case and for some its highly Selective (Mixed )

     

    How do we fix this issue

  • Joseph_Fadero Profile Picture
    Joseph_Fadero 666 on at
    Re: Updating Excel using office 365

    Hi @Danny_Dicaprio,

    It involves mixing in a couple of Power FX functions, Including the toUpper, toLower and some other functions to achieve that, I think this Link: Proper
    Can provide you further assistance on how you can get that done, Or you may want to try out the Encodian connector in Powerautomate.

    However I still believe the challenge can still be resolved in Excel Itself, You can simply utilize the Proper Function if all follows the proper format, or simply split the text into parts and use proper to join it, Also Flash fill could work, but you have to actually go to specific areas where the Data isn't providing the right result and change it, with that the flash fill AI will learn and find a meaningful pattern you are going for. 

    I hope I have been able to provide some assistance to your problem



  • Joseph_Fadero Profile Picture
    Joseph_Fadero 666 on at
    Re: Updating Excel using office 365

    Ok, Quick Question, What format will the email always be in?

    Is it always in Lowercase, or Proper case or it's highly selective

  • Re: Updating Excel using office 365

    @Joseph_Fadero 

     

    Hi,

     

    just Now I cross checked the file , not all emails have that issue , only for certain email I'm facing that issue.

     

    so, Flash fill won't work, I think.   it should be PowerFX

     

    Could you please explain how it can be done in PowerFX

  • Re: Updating Excel using office 365

    Hi @Joseph_Fadero 

     

    Thanks for the response

     

    I Think Flash fill will be a solution for me for this one , I'' ll check on how that works out for me 

     

    But if u don't mind could u l please explain How it can be done in  PowerFX

  • Joseph_Fadero Profile Picture
    Joseph_Fadero 666 on at
    Re: Updating Excel using office 365

    Hi @Danny_Dicaprio

    That is quite strange,

    But I have a simple fix for you, 

    To save yourself some time and perhaps headache, You can utilize flashfill (CTRL+E) in Excel (If you are using an Excel version >=2016) to populate the Values to that format using the first row as an example.

    e.g. If I have the email dannyd@gmail.com I can have another column by the side called DannyD@gmail.com.
    Now that solution though it is if it's a one time thing, You could actually utilize Formula's and Function in excel to help achieve your issue, Or You could Use PowerFX in Powerautomate to format it, It depends on which you are more familiar with 

  • Re: Updating Excel using office 365

    @Joseph_Fadero 

     

    Hi Thanks for response I tried it out, but Iam facing some issue 

     

    In my excel the  whole email is lower email  like dannydic@rm.com  but the email from the get user profile is DannyDic@rm.com

     

    Danny_Dicaprio_1-1710333358467.png

     

    Because of this it is showing as "No row was found with Id "

     

      is there is any way to fix the issue 

  • Joseph_Fadero Profile Picture
    Joseph_Fadero 666 on at
    Re: Updating Excel using office 365

    Hi @Danny_Dicaprio ,

    Good day,

    If I am not mistaken, The Image below is what you will like to achieve,


    Joseph_Fadero_0-1710329775082.png

    My steps below

    1. I am using List rows present in the Table to dynamically retrieve the data in the table, Once that was done, I brought up the get an email.
    2. Under the Get profile action, I placed the email dynamic content in the column requesting for ID
    3. Then next your Update a row action, You can use the email as the primary column and primary key value, and you can use dynamic column to update the rest of the values.
    4. Send an email will be based on the email dynamic content from the List an email.
      I hope you try it out and It works, You can also reach out to me on Linkedin and Twitter (Joseph Fadero).
      If the current process solves your problem, Please feel free to like the Post and Mark as a solution to help others with similar problems




Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,343

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,703

Leaderboard