PowerApp Link with Excel Data

[Seth18]

Greeting Everyone!

I am new to the PowerApps and needed help! Currently I am building an app to submit an expense claim in the company where we use Microsoft List as the backend for the app. So far for the employee's name we successfully make sure it auto detected the user who are using the app without letting them type in their names. But now, I have a data from an online excel file where it has the Staff ID, The employee's name, employee code, Function, Position and more. Now I also want that info to appear automatically just like the employee's name in the PowerApps based on its data value. May I know how can I do it? 

Looking forward to everyone replied! Thank you! 

[v-jefferni]

Hi @Seth18 ,

Is the Excel data formatted as a table? If it is, and the file is stored in a SharePoint shared library where everyone has read and write permissions (if in your OneDrive for business you need to share it to all the App users), you can use an Excel (Business) connector to connect the table from your App.

In your App, you can also use a LookUp function to find relevant info of the logged in user:

LookUp(Table1, EmployeeName = User().FullName).AnyOtherColumnsInExcelTable

Best regards,

[Seth18]

Greeting! 

Thank you so much for your response!

Yes  the excel data is formatted as a table and it also stored in a Sharepoint shared library. Forgot to mention that for the employee name i used the below code so that it detected automatically.

{

DisplayName:User().FullName,

Claims:"i:0#.f|membership|" & Lower(User().Email),

Email: User().Email,

Picture:""

}

But for the excel file as below i want the ID, the BU, the function to be also auto detected as the employee name how can i do this?

[v-jefferni]

Hi @Seth18 ,

I see the name in Excel table is different from the selected name in Combo box, and you don't have employee's Email address in the table, too. Within this circumstance, you can only search for the user in the table based on first name or surname:

First(Search(
    Table1, 
    EmployeeName, 
    //search on first name
    First(Split(User().FullName).Value   //Or Last() to search on surname
))

The result will be blank if nothing found, or a single record in the Excel table. You can use any column name in the table following above formula to reference the field value.

Best regards,

[Seth18]

Hi @v-jefferni,
Could you elaborate a bit more details on this? As i am a bit new here so it a bit confusing for now! 

[v-jefferni]

Hi @Seth18 ,

The name in Excel table is "KOSAL Proaseth" while the selected name in the Combo box is "Proaseth Kosal", so, you need to use either Proaseth or Kosal as the search text to search the name in the table. Using Split function it will be able to get first or last name from the full name:

Split("KOSAL Proaseth", " ")  -> ["KOSAL", "Proaseth"]

So, to search on the first name of the selected display name in the Combo box, here is the formula:

First(Search(
    Table1, 
    EmployeeName, 
    //search on first name
    First(Split(ComboBox.Selected.DisplayName, " ").Value 
))

Best regards,

[Seth18]

Hi @v-jefferni !

I still not quite understand yet! First of, Where would you want me to use the split formula? Since like i mentioned above for the combo box i used a code like this 

{

DisplayName:User().FullName,

Claims:"i:0#.f|membership|" & Lower(User().Email),

Email: User().Email,

Picture:""

}

While for the excel data the name is quite different from what the "code" generate the outcome in the box. Can you elaborate more details on it? As i want to see some data such as Staff ID, employee code, Function, Position like this and make them auto detected as well. 

Looking forward to your response back! Thank you!

[v-jefferni]

Hi @Seth18 ,

In the Combo box, the default selected item is based on current user info by using User().FullName and User().Email, so, you just want to find an entry in the Excel table based on current user as well. The Split function is not used alone but inside the second formula to get the first name or last name of current user. Using the name, you can search in the excel table for an entry. After getting it, you will be able to get all the field values.

For example, to get Staff ID, add Staff ID to the formula in the end.

First(Search(
    Table1, 
    EmployeeName, 
    //search on first name
    First(Split(ComboBox.Selected.DisplayName, " ").Value 
)).'Staff ID,'

Best regards,

[Seth18]

I'm quite lost here Jefferni! As i try using either the split or the second formula u mentioned in the datacardvalue of the employee name it does not work and it shows an error. I must be doing wrongly? The Employee Name (which you stated is a combo box) is a SharePoint Field which I take from it. How supposedly can I use your formula?

[v-jefferni]

Hi @Seth18 ,

The Employee Name column is a People column in SharePoint list. You should use the current formula in DefaultSelectedItems:

{
DisplayName:User().FullName,
Claims:"i:0#.f|membership|" & Lower(User().Email),
Email: User().Email,
Picture:""
}

For the Employee ID field which is a Text input so the Default property can be

First(Search(
    Table1, 
    EmployeeName, 
    //search on first name
    First(Split(ComboBox.Selected.DisplayName, " ").Value 
)).'Staff ID'

So it will populate the staff ID in the control by default on basis off the Combo box selection, and similar to the Department and Job Title fields:

First(Search(
    Table1, 
    EmployeeName, 
    //search on first name
    First(Split(ComboBox.Selected.DisplayName, " ").Value 
)).Department

First(Search(
    Table1, 
    EmployeeName, 
    //search on first name
    First(Split(ComboBox.Selected.DisplayName, " ").Value 
)).Position

Best regards,