Updating Excel using office 365

[Danny_Dicaprio]

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.

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.

[Joseph_Fadero]

Hi @Danny_Dicaprio ,

Good day,

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

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
   
   
   

[Danny_Dicaprio]

@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

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

  is there is any way to fix the issue 

[Joseph_Fadero]

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 

[Danny_Dicaprio]

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

[Danny_Dicaprio]

@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

[Joseph_Fadero]

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

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

[Joseph_Fadero]

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

[Danny_Dicaprio]

@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]

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

[Joseph_Fadero]

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