Combine Office365 Users and SP List!?!??!?

[Salamander94]

I'm creating a company directory.  I cannot for the life of me get a gallerey to display a sharepoint list of our production workers (they have no O365 account) and our O365 accounts together.  

Here's one of the codes I tried:

AddColumns(GroupBy('Production Directory', "Title", "UserNameGroup"),"Testy",AddColumns(UserNameGroup, "Phone", LookUp(Office365Users.SearchUser({top:500}), "Title" = DisplayName, UserNameGroup)))

I've also tried collection statements, but  I couldn't get that to work either.  

The only fields I have in the sp list are:  Title, Employee Photo, Department.

As for Office 365 I will only need:  DisplayName, Department, UserPhoto, OfficeNumber, MobileNumber.

Thanks for your help!

[Mr-Dang-MSFT]

Hi @Salamander94,

I've formatted your formula below for better understanding:

AddColumns(
    GroupBy('Production Directory', "Title", "UserNameGroup"),
    "Testy",
        AddColumns(
            UserNameGroup, 
        "Phone", LookUp(Office365Users.SearchUser({top:500}), "Title" = DisplayName, UserNameGroup)
        )
)

This means,

• "Group the Production Directory by unique Titles and call the resulting table UserGroupNames.
• For every table related to each of those unique Titles, add a column called "Testy, which is a repeat of the same UserGroupName table except that it appends a phone number that is looked up with the O365 Users connector."
• Side note, the lookup means, "Get the first 500 users in the organization. From that, get the first one whose DisplayName is verbatim "Title". Return the UserGroupName of the respective record." I don't think this is what you had intended.

An example record would have these 3 columns:

Title, UserNameGroup: [Employee Photo, Department], Testy: [Employee Photo, Department, Phone]

The first column is Title, which is text. The next two columns are tables of data [] with the given columns that I separated by commas.

Can you describe the columns you would like to have in the final product?

[Salamander94]

Wow you are brilliant and thank you for the help.  

So for my final product, I would like a single gallery.  This gallery would include Office 365 profiles with the fields for their display name, mobile phone, office phone, department, mail, and picture.  

I'd like to join them with an SP list of our workers without an office 365 profile.  On that list are the fields:  DisplayName (although it appears as "Title" in powerapps), Department, and User Photo.

The SP list fields are all "single line of text" fields.  

If possible I'd love to include the location from the O365 connector.

[Mr-Dang-MSFT · Accepted solution]

---

@Salamander94 wrote:

This gallery would include Office 365 profiles with the fields for their display name, mobile phone, office phone, department, mail, and picture.  

I'd like to join them with an SP list of our workers without an office 365 profile.  On that list are the fields:  DisplayName (although it appears as "Title" in powerapps), Department, and User Photo.

The SP list fields are all "single line of text" fields.  

If possible I'd love to include the location from the O365 connector.

---

By joining the two tables of content, I am assuming that you want the display names to fit under the same display name columns, department to fit under department etc. and overcoming how the column names may be different between the two sources.

In order to combine content like this, you will need to place the things you want into a collection. An action is needed to stick them together (Collect), and there is not a function that could passively display the two tables together. So something like AddColumns would not work here.

Office365Users.SearchUser() returns a bunch of fields. You only want this:

{DisplayName, mobilePhone, BusinessPhones, Department, Mail, OfficeLocation}

Note that Picture is not returned.

Your SP list:

{Title, Department, UserPhoto}

So merging the requirements together, you would want to achieve a record with a schema like this:

{DisplayName, mobilePhone, BusinessPhones, Department, Mail, OfficeLocation, UserPhoto}

Here's some skills involved:

• Collect: retrieve content from two tables
• RenameColumns: needed to make the columns of one table match the column names of another
• ShowColumns: pare down content to the desired columns

Big idea: start with the largest body of data first. Then shape subsequent smaller sets of data to that.

You can collect this table before viewing the gallery. OnStart of the app or OnVisible of the screen would be fitting.

// Start a table of all the O365 enabled users
ClearCollect(colUsers,
    AddColumns(
        ShowColumns(
            Office365Users.SearchUser({top: 500}),
            "DisplayName", "mobilePhone", "BusinessPhones", "Department", "Mail", "OfficeLocation"
        ),

        // Add a column that looks up each user's photo.
        "UserPhoto",Office365Users.UserPhotoV2(Id)
    )
);

// Add the non-O365 users from a SharePoint list. Match the column names.
Collect(colUsers,
    RenameColumns(
        'Production Directory',
    "Title","DisplayName"
    )
)

Reading inside out, top to bottom, this means, 

• Get the top 500 Users with O365 accounts
• Show only the desired columns
• Add a column to the table of 500 users that would show their associated picture
• Get the users from SharePoint
• Rename the columns from SP though so the content lines up in the right columns

One thing I will caution is that the UserPhoto column I suggested adding may likely not line up with the UserPhoto column in your SP list. This is because O365.UserPhoto has a data type of Picture and I'm assuming your UserPhoto column in SP is text. If this is the case, you'll need to revise things a little bit. If not, you should be good to go.

Important note: adding a column that would add the User Photo as a column is a very heavy operation--you're getting 500 pictures here with lots of calls and that may impact performance. I want to encourage you to think of what is the minimum necessary content. For instance, instead of collecting everybody at once, consider a scheme in which you perform a search against both O365 and SharePoint to significantly reduce the data you're pulling into the app down to 1 or 10 to make the actions you need.