SharePoint multi-Choice columns in Power BI

I WOULDN'T NORMALLY PRESUME TO OFFER ADVICE ON POWER BI, as it isn't my core skill. However, I was working on a dashboard to help my colleagues in Corporate Comms analyse the views-by-category of their Intranet news stories and ran into some unexpected challenges.

Those working in corporate environments will be familiar with the ideas of company objectives and employee behaviours, and we had been tagging our news stories with those values for some time (using custom columns added to the newshub's Site Pages library), but hadn't really tried to break down views and time-on-page by any of those metadata values. What complicated it massively was that it was possible for my Corporate Comms colleagues to tag stories with more than one value for Company Objective (for example).

So when I started setting up the dashboard, using existing data from the last 12 months, I was seeing some unexpected behaviour.

When I selected a single story in the dashboard, Power BI was telling me that there were 12 tags associated with that item, yet I could see from the SharePoint SitePages library that the item actually had only seven tag values.

In my limited experience of setting up other dashboards I knew that some SharePoint columns return a value of "Table", when viewed in Power BI Query Editor. All I had to do was expand those columns and we'd be on our way, right?

Because some SharePoint columns, like Choice, generate a Table rather than a Value, this is what you see in Query Editor.

But it wasn't that simple. After I had expanded the column, I found it was returning those phantom values to the dashboard.

Clicking on the little icon in the right of the column header brings up this panel. Selecting Label and clicking OK expands the column to show the actual Values.

Where had the five extra values come from? I was having real trouble figuring it out. Like I say, Power BI isn't my day job, so this was a knotty problem for me.

This is what the metadata columns looked like for that one item after expanding in Query Editor. There are 12 rows here when there are only a total of seven metatags applied to the item in SharePoint. What in the hootin' heck was going on?

Figuring that the world is full of smarter people than me, I posted the question in one of the Microsoft User Forums and waited to see if anyone could help.

THE SOLUTION

The Microsoft Community were very quick to explain that what I had ended up with by expanding the Choice columns the way I had was a Cartesian join. Instead of adding the tags from the three columns, it multiplies them. So two News category values, three DNA values and two Strategic priority values give me twelve instances of metadata tags.

2 x 3 x 2 = 12

Instead of expanding each of the columns, what I needed to do was create three separate Tables in Query Editor - one for each of the metadata columns - and connect them to the main table. Then I had to create a Measure to count up the metadata tags in a non-Cartesian way (we'll get to that). That was helpful, but still lacking a bit in detail. After managing to fill in the gaps in the process, I thought I'd post an account of how I did it here to save anyone else hours of fiddling to reach the same result I did.

With your Dashboard file open in Power BI Desktop app, click on Transform Data to open the Query Editor.

Click the image to enlarge.

In the left hand column of the Query Editor, right-click on the existing table and select Duplicate from the fly-out menu.

Click the image to enlarge.

You now have a copy of the main table named Site Pages (2). In the new table, select the News Categories column.

Click the image to enlarge.

Now scroll right as far as you need until you find the new table's ID column. Control-click that column to select that, as well.

Click the image to enlarge.

Now right click on the header of the ID column and from the fly-out menu select Remove other columns.

Click the image to enlarge.

You're left with just the News Categories and the ID columns in the new table.

Click the image to enlarge.

At this point, you might want to think about renaming the new table to, say, NewsCat so you can identify it easily.

Click to enlarge.

The last step of this part of the process is to expand the News categories column, so that the values can be read by the Dashboard visualisations. Click on the arrows icon on the right of the column header, select Label and click OK.

Click the image to enlarge.

Repeat the process for any other Choice columns you need to include in your Dashboard. When that's done, close the Query Editor. Now, in the Home view of your dashboard file, click on the Model View icon, and join the tables (ID > ID) as shown.

Click the image to enlarge.

Final bit of prep work for me was to create a Measure called "idCount" in the main Site Pages table:

idCount = DISTINCTCOUNT('Site Pages'[ID])

Now you can add, for example, a pie-chart visualisation to the Dashboard. Drag the News Categories.Label column from the NewsCat table to the Legend field, and the idCount measure to the Values field, and that should show you accurately how many times tags from each of the three columns have been added to news stories.

Click the image to enlarge.

OPTONAL EXTRA

The other feature I added was a panel in the Dashboard that showed the average number of metatags that had been added to news stories - both by story and by metadata column. The earlier version (see the screengrab at the start of this entry) had been returning the incorrect, Cartesian values. To get it to return the correct values, I needed to add some new Measures to the Dashboard.

First, I had to count the metatags, which required a slightly different approach. Here's the script for that Measure (TagsCount):

TagsCount = VAR NewsCount =
    CALCULATE(
        DISTINCTCOUNT('NewsCats'[News categories.Label]),
        ALLEXCEPT('Site Pages', 'Site Pages'[ID])
    )
VAR StrategicCount =
    CALCULATE(
        DISTINCTCOUNT('StratPrior'[Strategic priorities.Label]),
        ALLEXCEPT('Site Pages', 'Site Pages'[ID])
    )
VAR DNACount =
    CALCULATE(
        DISTINCTCOUNT('DNA'[DNA.Label]),
        ALLEXCEPT('Site Pages', 'Site Pages'[ID])
    )
RETURN
    NewsCount + StrategicCount + DNACount

Click the image to enlarge.

Then I created another Measure (TagsAverage) to calculate the Average Tags per story, which takes the output from the TagsCount Measure and calculates the average I needed:

TagsAverage = AVERAGEX(
    VALUES('Site Pages'[ID]),
    [TagsCount])

Click the image to enlarge.

The final task was to update the Card visual displaying the Average Tags per story with the Measure TagsAverage. And now the Card was displaying the correct count of seven - which is what I'd wanted all along.

Click the image to enlarge..

All-in-all, not terribly intuitive, but the usefulness of the final Dashboard made the challenge worthwhile.

Click the image to enlarge.

I hope that's been helpful to someone.

Link Content Types in Power Apps

AS ANY INTRANET MANAGER WILL TELL YOU, THE LAST THING YOU NEED is for your Site Editors to take copies of documents from elsewhere in the Intranet to add to their own document libraries. This will only result in multiple copies of the same document throughout the Site Collection, throwing up links to outdated versions of the same document in the Search results and causing confusion for the site visitors.

The problem was very real in SharePoint Classic, but with SharePoint Modern, Microsoft introduced the Link Content Type for Document Libraries, which meant that you can include Links to remote documents in your Library and even apply metadata to help organise those Links in the same way you would with actual documents.

Click image to enlarge.

So, if we can get site editors to change their habits and use Link Content Types instead of multiple copies of files, everything will be just great? Well, kind of ...

I ran into a bit of a challenge when creating a Power Apps searchable Gallery for a document library that uses a lot of Link Content Types. While the documents would open readily from the Gallery, the Link Content Types just downloaded shortcut files to the PC's local Downloads folder. Not really the behaviour I was looking for.

So I went to Google to try to find how a Power Apps Gallery could launch a document from a Library Link Content Type. I think that search term captured all the key words. I found just one post in the Microsoft Power Apps Community site that tried to tackle the problem.

In that post, the first solution offered was to add "?csf=1&web=1" to the end of the Launch Expression, which sort of, kind of works. But it's a pretty ugly result.

Click image to enlarge.

Your visitors would need to know to click on the tiny and obscure Open button at the top right of the screen. That wasn't going to work for me. 

Then, right at the foot of that same post, there were some instructions for using an embedded Flow to extract the URL from a Library Link file. The explanation was lacking in detail, so I set about decyphering it to figure out how to make it work for me.

SET UP THE FLOW

Let's start with creating the in-App Flow. Click the ellipsis at the foot of the Power Apps left hand navigation and select Power Automate from the fly-out menu.

Click image to enlarge.

Click Create new flow and select the Create from blank button. Name your Flow now (I called mine getURL). This is important because first time I tried this I didn't and adding the name after working on the Flow proved trickier than it needed to be.

Click image to enlarge.

When the Flow opens, the first Action - Power Apps (V2) - is already there. Expand the panel and add the value for the Document Library we're going to call later. I've labelled it "FilePath", here. Not the best label - it probably should have been "DocLibrary" - but it really doesn't matter. The key info is the pathway to the Library, which I've put in as:

/DocsMNTraining/

Click image to enlarge.

The next Action we need is the Get file content using path. We add the site address and then the output from the previous action. These will then be combined to render the full pathway to the Library Link in the Document Library.

Click image to enlarge.

Finally, we need to gather together everything from the first two actions and write the output into a variable for the Power Apps Gallery to process. Add the Action Respond to a Power App or flow. I called the variable "urlValue" and set its value to:

last(split(base64ToString(outputs('Get_file_content_using_path')?['body']['$content']),'URL='))

You can do this by adding the above script to an expression.

Because the Get file content using path action extracts the Link file's content as Base64, we need to convert it to a string before the Power Apps Launch function can recognise it (no, I didn't know that either). Bear with me, all will become clear.

Click image to enlarge.

I mentioned at the start of this piece that trying to launch a Library Link Content Type from a Power Apps Gallery caused a shortcut to be copied to my Downloads folder. So I opened one of them and took a peek inside. This is what I saw:

Click image to enlarge.

You can see that the actual URL is preceded by "URL=", so our formula above is saying, "grab everything after "URL=" and convert it from Base64 to a text string.

So far, so good.

... AND BACK TO THE POWER APP

When setting up a SharePoint Document Library in a Power Apps Gallery in the past, I've just used an HtmlText box and put in a formula something like this:

Concatenate("<a href='https://xxxxx.sharepoint.com/sites/SiteName/",ThisItem.'Full Path',"' style='Text-Decoration:None;'><b>",ThisItem.Name,"</b></a>")

I use ThisItem.'Full Path' in preference to ThisItem.'File With Extension' because it makes for a cleaner link and the icon (which we'll get to later) is enough to tell visitors what kind of file to expect. And that works fine for "real" files. But with URL links, we have to trigger the Flow to process the link information, so a different approach is needed. And that's where the Launch function comes in.

The formula we need is something that will kickstart the Flow, extract the Link URL and then action it from Power Apps, so something like this:

Launch(getURL.Run(ThisItem.'Link to item').urlvalue)

What that does is fire up the Flow, which compiles the Link's URL from the value ThisItem.'Link to item', converts it back into a string and writes the value to the variable urlvalue. The formula then Launches the value in the urlvalue variable.

That's all fine and dandy for processing URL links stored in a SharePoint Library. But what about the actual files - PDFs .docx, etc? Well, we just need to combine the formula into an If statement and we should be good to go. Like this:

If(
    Lower(Right(ThisItem.'File name with extension', 3)) = "url",

    /* Item is a SharePoint .url file */
    Launch(
        getURL.Run(
            ThisItem.'Link to item'
        ).urlvalue
    ),
    /* Item is a normal file */
    Launch(
        ThisItem.'Link to item'
    )
)

What we're saying here is ... if the last three characters on the right of the filename are "url", run the Flow. If not, just use the regular ThisItem.'Link to item' value. The "lower" is just a precaution in case the Link's suffix is ".URL" instead of ".url".

Then, we need to deploy the solution into the Gallery. My first thought was to just put in a Text box displaying the FileName, then add the above formula to the Text Box's OnSelect property. And that does work. But the problem is, there are no visual indicators that the Text Box is a link - no dynamic underline, no mouse rollover state, so not ideal. And there aren't any Cursor or OnHover properties in a Text Box.

After a bit of head-scratching I finally settled on a Button, making the fill transparent, adding an underline to the text. The cursor change on rollover is part of the default Button setup.

An optional extra I thought worthwhile was to also tackle the icon displayed next to the Library item. A quick Google will throw up the formula for extracting the Microsoft file icons and the PDF one. But there is no default icon for Library Links. So I made one. And because it's a custom icon, I stored it in the Site Assets library, then wrote an If statement for the Image box.

If(Lower(Right(ThisItem.'File name with extension',3)) = "url",
    https://xxxxx.sharepoint.com/sites/SiteName/SiteAssets/Link.png,
    https://res-1.cdn.office.net/files/fabric-cdn-prod_20220127.003/assets/item-types/32/ & Last(Split(ThisItem.'File name with extension',".")).Value & ".png"
)

That should be everything you need to know to get a Power Apps Gallery to display functional Link Content Types (from a SharePoint Library) alongside regular files.

Click image to enlarge.

I hope this helps someone.

Setting DefaultSelectedItems in a hard-coded ComboBox

I WAS PUTTING TOGETHER A SIMPLE FORM for our company's branch offices to report on what Business services they have in place. Over the years, I've learned ways to keep forms as simple as possible, and the degree of simplicity will inform whether I can use a Customised SharePoint form or have to go full Canvas App. In this case it was just eight fields, so a customised SharePoint formset should do the job.

The next step on my simplification journey is, wherever possible in my datasource List, use Single line of text fields, as Lookup and Choice fields complicate things unnecessarily. I can let PowerApps DropDowns and ComboBoxes do the heavy lifting.

For simple forms, the embedded SharePoint "Customise forms" method will work just fine.

So, the first two fields are Country and Office. In most European countries, we just have the one office, but in a couple we have three or more offices. So a simple cascade was the way to go.

Pretty sure I've outlined this elsewhere in the blog, but here's the short version. Set up a separate SharePoint List (Locations) with a column for Country and a column for Office. I don't like to mess with the Title column, so I keep that as is, but set the default value to "Edit this item", so it automatically populates if I add any further locations.

I prefer to give my lookup columns headers that make sense, so if I have to make changes later, I don't waste time wondering what function the "Title" column feeds.

Like I say, the main SharePoint List uses Single Line of text where possible, so these first two fields are populated using DropDowns that cascade. The set up for the Country DropDown looks like this:

ddCountry.Items = Distinct(Locations,Country)

Country_DataCard1.Update = ddCountry.SelectedText.Value

Country_DataCard1.Default = ThisItem.Country

And the Office DropDown is configured this way:

ddOffice.Items = Distinct(Filter(Locations,Country = ddCountry.SelectedText.Value),Office)

Office_DataCard1.Update = ddOffice.SelectedText.Value

Office_DataCard1.Default = ThisItem.Office

If you set it up this way, then the Default for both DropDowns can be:

Parent.Default

Setting it up this way means that we end up with cascading drop-downs. Select France in the Country drop-down and the Office selection is limited to just French locations.

All pretty easy so far, but the next bit was a challenge. I didn't really want to set up another SharePoint List with just a single column for the Services used by each office, and tacking it on as a third column in the Locations list would have meant ballooning it up from 15 rows to 135 rows ... which didn't seem sensible. I'm not keen on Choice columns with more than four or five items, and they can be a bit of a pain in PowerApps forms, so I thought I'd try a slightly different approach and see how it worked out. I was going to hard-code the list of services into a ComboBox, especially since I didn't think we'd be adding any new Facilities services any time soon.

So, the set up of the Service ComboBox, looks like this (number of Items shortened for the sake of brevity):

cbService.Items = ["Reception","Switchboard","Catering" ...]

Service_DataCard1.Update = cbService.Selected.Value

Service_DataCard1.Default = ThisItem.Service

The tricky part for me was how to set the "Default" of the ComboBox for when the Form was in Edit mode.

If I'd been using a SharePoint Lookup list I could just set the ComboBox DefaultSelectedItems to:

cbService.DefaultSelectedItems = ThisItem.Service

But with a hard-coded datasource, that doesn't work. So off I trekked to Google. Incredibly, I couldn't find a definitive answer ... but after a couple of hours of searching, I was able to piece together from various sources the principle of how this should work. Essentially, it's this:

Filter([Datasource], Value = ThisItem.Service)

So what I had to put in was this:

cbService.DefaultSelectedItems = Filter(["Reception","Switchboard","Catering" ...], Value = ThisItem.Service)

Now when I edit an item, the stored value is displayed in the Service ComboBox.

I hope that helps someone.

Problems with Lookup Columns when copying item metadata from one library to another

DURING 2024, MICROSOFT HAS BEEN DEPRECATING FUNCTIONS IN CLASSIC SHAREPOINT. Needless to say this has been causing me problems with the legacy Teamsites with our business relies upon.

The first time I noticed this was in July 2024 when Microsoft turned off our ability to edit the landing pages of Classic Teamsites. I know there are workarounds where the Tenant Admin can fiddle with the SharePoint security settings, but I don't have that level of access and, more importantly, my Teamsite Owners don't.

The next change was in November 2024 when Microsoft turned off the Content Editor and Script Editor web parts, meaning that even if we could edit the landing page, we still wouldn't be able to change or add text, or embed any Power Platform functions on the page. This was real problem for me because one of my clients had asked that I revise a PowerApps Gallery to include additional Archive Libraries. I was able to do the PowerApps part of the work (more on that in a future post) but then wasn't able to embed the new Gallery on the page ... so that was very nearly a wasted effort.

I understand that Changes Happen and that while we're hosting Classic and Modern in the same site farm, there will have to be compromises in the functioning of Classic sites ... but I'm still left with the challenge of circumventing these issues.

There has to be an easier way of transferring data from one list or library to another, right?

It all came down to one not-so-simple solution - I'd have to migrate the functions from Classic to Modern SharePoint.

It's not difficult to make copies of PowerApps and it's not hard to clone Lists and Libraries from Classic to Modern in the same Tenant. The real challenge is to find a way to copy the data from those lists and libraries to the cloned Data Sources.

There are PowerShell methods and some third party tools that can be used to copy data from one place to another in SharePoint but again, that requires Tenant Admin access and that wasn't available to me. A brief conversation with the actual Tenant Admin ended up in a "Dev-UAT-Live, months of work, yadda-yadda" exchange. I haven't used Dev-UAT-Live methodology since the good old days of Visual Interdev/ASP/.NET. All I wanted to do was migrate some data, preferably keeping the Created/Modified dates, but it wouldn't be the end of the world if that data was lost in translation.

It was going to have to be a Power Automate Flow.

Only one of my business solutions uses Document Libraries. Figuring this would be the most difficult, this is where I decided to start.

The several libraries in this solution all had an identical structure (some are live, some are archives) - over 50 columns, some of which are lookups. Even so, I didn't think this would pose much of a problem, as the migration should be possible using just a few steps on Power Automate.

This was my first try:

Not shown here, but you can expand the Get file properties' "Show advanced options" and limit the retrieved items to just one View of the library. This is what I did for testing purposes. Click on the image to enlarge.

... and it almost worked. For some reason the values for one of the Lookup columns, "Class", wasn't migrating across. Which was puzzling me, because the value for "LOB", also a Lookup column, was ported across just fine.

As you can see, the column for Class remains blank.

So, thinking that I must have done something wrong, I tried adding an Update file properties function, which I thought might force the Class column to update. But that didn't work either.

There is a clue here, but I just hadn't spotted it yet.

A bit of Googling around "Lookup column data not migrated by Flow" didn't get me anywhere, either ... and finally, I posted a question on the Microsoft Power Automate community site. One helpful person suggested that I remake the Lists that supply the main library's lookup columns. I had already tried that, but the comment did get me to thinking ...

Lookup columns don't use the Value from the feeder list ... they use the ID. So I took another look at the source feeder list and the destination feeder list. And this is what I saw ...

The IDs of the corresponding Lookup values were different. So, during the migration process, the Flow was looking for a Class Value with the ID of 63 in the destination Lookup column and not finding it.

It looked to me like the original builder of the Libraries had created the Lookup list for Class, then added and deleted the values a few times, ending up with a list of values where the lowest ID was 63. When I had cloned the Library across to the destination Modern site, I had added the values for the Class Lookup column and naturally the ID numbers started with 1. Deleting the Lookup list and starting again just repeated the problem.

The solution was to first add (and delete) 62 items to the Lookup list, then add the correct values, slowly, one-at-a-time, to get the ID numbers in the two Lookup lists to correspond. Once I'd done that I could just run the Flow (as pictured above) and everything worked fine ...

This time the Flow worked perfectly, because the IDs in the source and destination Lookup lists matched.

SAVING THE CREATED AND MODIFIED DATES

I did fret a little about this. While it's not possible, at least with a Flow, to replicate the Created and Modified dates from one List/Library to another, it did occur to me that I could add two new date columns to the destination Library - "Legacy_Created" and "Legacy_Modified" - and use the Flow's Update file properties function to copy the dates from the source Created column to the destination's Legacy_Created column.

I hope that helps someone.

PowerApps - Retrieve an Employee's ID (employeeId)

YOU'D THINK IT'D BE EASIER TO GET THE EMPLOYEE ID, RIGHT? Just add Office365 as a data source to your Power Apps form then call:

Office365Users.UserProfile(ThisItem.Id).employeeId

... or something similar. But no. Can't do it that way.

As is my habit with this sort of thing, I went first to Google. And from what I could see there wasn't a way to get the employeeId natively in PowerApps. Any solution I found wanted me to run a PowerAutomate Flow, which wasn't what I was after.

Then I had a breakthrough. I hadn't realised it before, but there is also a flavour of Power Automate you can call directly from a Power App - it really is a constant learning journey, this PowerApps stuff, isn't it?

WHAT I NEEDED IT FOR

There are probably many scenarios where you might need to know a colleague's employeeId. By that I mean the multi-digit label (often sourced from WorkDay) that Active Directory copies and uses to identify a colleague. In the case of our corporation, it's a six digit number that you can see in Delve but can't otherwise access, at least not programmatically.

Why does Microsoft display the Employee ID in Delve, yet won't let you retrieve it via the Office365Users method?

In this case, I was looking to allow colleagues to book a session with the official corporate photographer. In order to differentiate between two John Smiths, we name the images in the format:

FirstName_Surname_EmployeeID

Then they all get stored in a SharePoint Document library and I can showcase them to the Business using a SharePoint Gallery (out of scope for this blog post).

HOW I DID IT

The first part is fairly easy. I created a SharePoint list with a particular set of columns then, to keep it simple, customised the list's native forms via Power Apps.

There are other columns in the application, but these are the minimum needed to make the Get Employee ID function work.

The key columns were:

• Timeslot (Text)
• UserName (Name)
• PhotoFileName (Text)
• Location (Text)
• Date (Date)

This is what the Gallery looks like ... just one timeslot added for demo purposes.

To make the booking form available to fellow employees, I created a Power Apps Gallery and set it up to display the vacant sessions which colleagues could reserve by clicking on "Book this timeslot" (a link to the list's EditForm). Once the Timeslot was booked, I made it invisible by having the Gallery check whether the PhotoFileName cell in the list was empty. I may discuss the building of the Gallery in a future blog if anyone is interested.

So, to get started with customising the List's EditForm, let's click on Integrate and select from Power Apps the Customize forms option from the drop-down's flyout menu.

This is pretty simple customisation, so no need for a Canvas App.

Before starting an customisation on the form, I needed to add Active Directory as a datasource, because that's where the employeeId is stored.

Add data, then select Office365Users directly.

I didn't want our registrants to mess with the Date or Timeslot fields, because they're fixed for each Photography session. So I added a Text Label to the DataCard and made it a concat of the Timeslot and the Date, separated by a comma, like this:

DateTime_Label.Text = 

Concatenate(
Text(DateTimeValue(ThisItem.Date), "dd/mm/yyyy"),
", ",
ThisItem.Timeslot
)

To display the Time and Date, add a Text Label and use the Concat function to join the two.

Next, I set up the UserName field to get the email of the current user - I'm going to use this value in the Flow that will retrieve the employeeId:

UserName.Default = Office365Users.MyProfile().Mail

Then I added a Text label to the PhotoFileName field, to start building the final filename for the image that will be added to the directory once the Photographer's done his work. This will be in the format:

FirstName_Surname_

I did this by adding this script to the Text value of the label:

Photofile_Label.Text =

Concatenate(
Office365Users.MyProfile().GivenName,
"_",
Office365Users.MyProfile().Surname,
"_",
vEmpID.empid
)

The value for the variable "vEmpID" will be generated by the Flow. We're just setting up the form to receive the value when the Flow delivers it.

The UserName field captures the email of the current user and the PhotoFileName field compiles a string of the user's name and their Employee ID.

Finally, for this part of the process, set the Update value of the PhotoFileName DataCard to fetch its value from the PhotoFile_Label.

PhotoFileNameDataCard.Update = Photofile_Label.Text

We'll come back to the EditForm shortly, but now let's set up the Flow ...

ADD AN EMBEDDED FLOW TO THE FORM

So the trick to fetching the current user's employeeId value is to add an embedded Flow to the PowerApps form. This was quite a revelation to me, but a welcome one. To embed a Flow in PowerApps all you need to do is to click on the Power Automate icon in the PowerApps left-hand column. Then, give the Flow a name and start building it, the same way you would in the Power Automate application.

Each Flow you create in your PowerApps environment will need a unique name.

Once you have created the Flow, you'll need to edit it to add the functions, so click on the ellipsis then select Edit from the flyout menu.

I called my Flow "GetIDPB", because I had another Flow in my PowerApps environment called "GetID" ...

The first item you'll need to add to your Flow is the PowerApps function. This is what will take the current user's email address and use it to retrieve the corresponding employeeId. Add "Email" as the Input type and put the Form field value in the adjacent field, like this:

The name of the form field that holds the current user's email address will be different in your form.

Next, we're going to add the function, Get user profile, click on the Advanced option and add the output from the previous function into the User (UPN) field and add "employeeid" into the Select fields field.

This is the step that goes off and finds the Employee ID.

Now we add a Compose function and make the Input the Output from the previous Get user profile function.

I'm not exactly sure what this step is doing, but just go with it ...

For me, the Dynamic Content menu didn't offer me the Output from the Get user profile function, so I had to add it as an expression, like this:

outputs('Get_user_profile_(V2)')?['body/employeeid']

The next step is optional, but I found it handy during testing to make sure the Flow was triggering when I needed it to and that the Employee ID was being captured, like this:

You can always remove the Send an email function when you're happy the Flow is performing as expected.

We're going to hold the Employee ID in a variable, so we can deliver it back to the Power App form, so let's initialise one.

No need to populate Value, we'll assign one to the variable in the next step.

The last step is to deliver the result back to the Power App form, so that requires the Respond to a PowerApp flow function. Just add the variable name and set its value to the output from the Compose function. It does seem like overkill because I added the same value for the variable in the next step, as well ...

We're setting the value of the variable in this step and sending it back to the PowerApps form.

And that's it ... we're ready to return to the Form and make sure the PhotoFileName field is ready to receive the value for the Employee ID.

But don't forget to Save your Flow first.

AND ... BACK TO THE FORM

I'd already set up the PhotoFileName field to receive the value from the EmpID variable once the Flow had run. The next challenge was to come up with a way to trigger the Flow.

In most scenarios, we ask the user to make some sort of change to the form, and we can usually piggyback on that change to trigger other actions. But in this case, I just wanted the person booking the session with the photographer to check that the timeslot was correct and then click the Save button. The rest I wanted to happen automatically and invisibly.

My first thought was to use a Timer ... so I added one to the Form and set it up like this:

Timer.Duration = 500  --  in milliseconds
Timer.Start = false
Timer.Autostart = true
Timer.OnTimerEnd = Set(vEmpID, GetIDPB.Run(DataCardValue7.Text)

Adding a Timer button seemed like the right choice, but at first it wouldn't trigger the Flow.

But that didn't work. For some reason, the Timer button would not launch the command that starts the Flow running. I tried setting the Start function to "true" but that didn't help either.

After some minutes of deep thought I came up with a solution. Swap the actions of the Timer button and the Save button.

On the Timer button I set the Autostart value back to "false". Then I gave the Start function the value of (variable) "vStart". Finally, I set the OnTimerEnd value to what I'd originally put in the Save button:

SubmitForm(SharePointForm1);Navigate(FormScreen_Thanks,ScreenTransition.Fade)

And on the Save button, I changed the OnSelect value to:

Save.OnSelect = Set(vEmpID, GetIDPB.Run(DataCardValue7.Text));Set(vStart, true)

... note the addition of the command that sets the vStart variable to "true".

Now when I clicked on the Save button, the Flow was triggered, the Employee ID was retrieved, the Flow sent me an email containing my Employee ID and the Form updated the SharePoint List field PhotoFileName with my First Name, Surname and Employee ID.

Satisfied that all was working as expected, I could now hide the form's PhotoFileName and UserName fields, along with the Timer button.

After hiding the fields and functions the user doesn't need to see, this is what the final triggered EditForm looks like.

The one final test to make sure all was working before removing the Send an email action from the GetIDPB flow ...

After clicking Save, the SharePoint list will be updated to look like this.

And that's it. Maybe in the future Microsoft will make this process a bit simpler, but until then, this should suffice.

I hope this helps someone.