Getting Excel calculated data in Power Apps using Flow: RegExp method

Ever wanted to get data from Excel using Power Apps? by using Canvas apps connectors is pretty straight forward:

• Configure your OneDrive / OneDrive for Business connector to get the desired Excel file. Once you select it, you will get a list of tables defined in that file to pick up you data.
• After that, design the app to your heart’s content, add a Gallery, attach your previously selected table to it and voilà!, you’ll see the data.

… or not.

Where is our data? if we check our Excel file we will see the data but, there is a catch, the data that we are trying to get is the result of calculated cells.

The blue table is the actual data and the orange table just shows the result of a formula calculating data from the blue table. This data is not visible through the connector in Power Apps. The connector opens the files in a manner that doesn’t trigger the actual Excel engine that does these calculations by default. My guess is that is treating these files as XML-based files.

What can we do to solve this issue?

Microsoft Flow to the rescue

When the Power Apps connector didn’t get the data, I’ve decided to give Flow a shot. I know that both share the Power Platform connectors but, being the platform of choice for integrations, Flow sometimes does it’s magic in a different way.

Querying the data

As we are going to connect this Flow to Power Apps, the first part should be to create it as an Instant Flow with the main trigger being Power Apps. This will make it ready to be called from Power Apps.

The next step will be to add the Excel action “List rows present in a table” and once we set it’s parameters, we can test to see if we get the calculated data.

So now that Flow saves the day, let’s get this data back to Power Apps.

Shaping the response

From the previous screenshot you can see that besides our cell data, we are also getting all sorts of metadata. We can shape this data into pieces that have more meaning.

For this, we’ll use the Select action. As it names implies, it’s going to select the specific data you need from the previous result.

The “From” section gets the result of listing the rows and the “Map” section allow us to model the data as we see fit. In this case, we only want the “Name” and “Years to retirement” cell data.

Better, right?

Now that we have this data structure, you might be temped to swing this back to Power Apps and be done. That would be wishful thinking because this is the only data types allowed to be “responded” to Power Apps:

That’s OK though, now it’s Power Apps’s turn to do it’s magic 🧙🏼‍♂️

Re-Shaping the response

To deal with this, we’ll use another data action called Join to tie all data with our favorite tie ( bad pun I know ). For this example, we’ll use the semicolon character to get one whole string and then return that to Power Apps.

This will be the actual data Power Apps will get:

{
  "response": "{\"Name\":\"Mark\",\"Age\":\"38\"};{\"Name\":\"Carrie\",\"Age\":\"15\"};{\"Name\":\"Jhon\",\"Age\":\"6\"}"
}

Now it’s Power Apps turn to shine

On this side of the Power Platform, we need to connect the Flow we created earlier by going into the Action tab and then Flow. Power Apps will show the list of Flows in your tenant for you to select. Mine is called “Excel calculated”, so after selecting it, I can call it like this:

Excelcalculated.Run()

We can call this Flow at the OnStart action and we could also process the data structure. It’s a whole string so the first step would be to untie this into an array of data by using the Split method.

Set(
    Data,
    Split(
        Excelcalculated.Run().response,
        ";"
    )
)

Keep an eye on the “.response” part, this is to link the response of the Flow to the Split method.

Now that we have an array of these structures, we need to find a way to de-structure it into a collection for easier access to our Gallery. This is where Regular Expressions, once again, become very helpful.

This time, with an special guest appearance of one of the newest functions, With. It’s a kinda complex but very powerful function: it allows you to perform actions or calculations on records. Mix that with the ForAll function and you’ll get a very flexible record processing engine.

Unscrambling structures with Regular Expressions

Set(
    Data,
    Split(
        Excelcalculated.Run().response,
        ";"
    )
);
ClearCollect(
    Values,
    ForAll(
        Data,
        With(
            Match(
                Result,
                "\{\""Name\""\:\""(?:(?<name>\w+))\""\,\""Age\""\:\""(?:(?<age>\d+))\""\}"
            ),
            {
                Name: name,
                Age: age
            }
        )
    )
)

This code is taking our array for a joyride:

• ForAll runs through the array in parallel threads
• With executes the regular expression formula by using the Match function and then exposes the result into the name and age variables.
  • This expression uses capture groups to extract both fields into one record: (?:(?<name>\w+)) and (?:(?<age>\d+))
• Finally, we get all these data into our collection using ClearCollect.

The result? after linking our collection to the Gallery, well, see for yourself:

Thanks for reading this far, I think this is one of my longest post so give yourself a pat on the shoulder! this was a lot of Power Apps and Flow stuff. As always, take the bits for a spin here and be sure to check all other goodies in the main repo.

Never stop learning!