Looping through Excel datasource rows

Question

I am using Excel table as a data source and I have also added Office365Users data source.

Now, the Excel table looks like this

Name  Age Location
John  30  US
Mike  32  Canada

It is formatted as a table in the Excel so it is a proper data source it is called users

What I am trying to do is to use Office365Users.MyProfile().DisplayName and match it with the name from the Excel row.

So the end result would be that the app would display only my name, age and location rows.

After hours of struggling I haven't gotten anywhere with this, I just cannot figure out how to loop through the "Users" table and then match the Name column and display only that row.

If someone could help me or perhaps have a similar example I would be very grateful!

carlosfigueira carlosfigueira · Accepted Answer · 2020-05-20T12:02:19

You can use the LookUp function to find the row that matches your display name. For example, if you have one label where you would show the user age, you could set its Text property to something like

"Age: " & LookUp(MyExcelTableName, Name = Office365Users.MyProfile().DisplayName, Age)

And similarly to display the location:

"Location: " & LookUp(MyExcelTableName, Name = Office365Users.MyProfile().DisplayName, Location)

You can also improve a little the performance of your app by caching the result of the MyProfile() call, and possibly the LookUp as well, so you won't need to make the network call multiple times. For example, in your screen where you have your labels, you can have this expressions in the OnVisible property:

Set(myProfile, Office365Users.MyProfile());
Set(myInfo, LookUp(MyExcelTableName, Name = myProfile.DisplayName))

And your label texts would become

"Age: " & myInfo.Age
"Location: " & myInfo.Location

Hope this helps!

Looping through Excel datasource rows

1 Answers