Getting Excel calculated data in PowerApps using Flow: RegExp method

calculated data

Ever wanted to get data from Excel using PowerApps? 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.
No data showing

… 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.

Excel data

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 PowerApps. 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 PowerApps 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

Query the calculated data

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

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 PowerApps.

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.

Select action

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.

Selecting data

Better, right?

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

Where are my complex outputs?

That’s OK though, now it’s PowerApps’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 PowerApps.

Join action

This will be the actual data PowerApps will get:

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

Now it’s PowerApps 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. PowerApps 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:

Gallery with data

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 PowerApps 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!

Leave a Reply

Your email address will not be published. Required fields are marked *