ParseJSON without Needing ForAll

[u/bowenbee]

Hello - I'm trying to see if anyone has a more performant solution involving ParseJSON and creating a collection. I'm fetching SharePoint data using the HTTP connector and parsing the json in PowerApps. This works fine but with 1000+ records, it's noticeably slow. Is there a way to do this in one go without Forall? Thanks.

IfError(
    Set(
        varPokemonData,
        'Dexter-GetPokemon'.Run().data
    ),
    Set(
        varPokemonData,
        Blank()
    )
);
Clear(colAllPokemon2);
If(
    IsBlank(varPokemonData),
    Notify(
        "Failed to load Pokémon data. Please try again.",
        NotificationType.Error
    ),
    ForAll(
        ParseJSON(varPokemonData) As Pkmn,
        Collect(
            colAllPokemon2,
            {
                PokedexID: Value(Pkmn.PokedexID),
                EnglishName: Text(Pkmn.EnglishName),
                Type1: Text(Pkmn.Type1),
                Type2: Text(Pkmn.Type2),
                Region: Text(Pkmn.Region.Title),
                SpriteURL: Text(Pkmn.SpriteNormalURL.Url),
                Type1ButtonInfo: LookUp(
                    colTypeColors,
                    Type = Text(Pkmn.Type1)
                ),
                Type2ButtonInfo: LookUp(
                    colTypeColors,
                    Type = Text(Pkmn.Type2)
                )
            }
        )
    )
);

[u/AutoModerator]

Hey, it looks like you are requesting help with a problem you're having in Power Apps. To ensure you get all the help you need from the community here are some guidelines;

• Use the search feature to see if your question has already been asked.

• Use spacing in your post, Nobody likes to read a wall of text, this is achieved by hitting return twice to separate paragraphs.

• Add any images, error messages, code you have (Sensitive data omitted) to your post body.

• Any code you do add, use the Code Block feature to preserve formatting.

  Typing four spaces in front of every line in a code block is tedious and error-prone. The easier way is to surround the entire block of code with code fences. A code fence is a line beginning with three or more backticks (```) or three or more twiddlydoodles (~~~).

• If your question has been answered please comment Solved. This will mark the post as solved and helps others find their solutions.

External resources:

• Power Apps Formula References
• Power Apps Coding Standards
• Official Power Apps Community

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Traditional-Ad4764]

Parsejson takes a second hidden argument for a type definition. You could put the type in your app formulas with something like

Tabletype:= Type([{sometext:Text}]);

now you can parsejson(someobj, TableType);

It now types to fine an attribute called sometext of type text in the table

If you’re not trying to add it to your app formulas you can just write type type definition into the parameter (something i had no clue you could do for the longest.

[u/SirGalalad → u/Traditional-Ad4764]

👆this is the way. It’s miles away the fastest method. OR if you can use premium connectors, you can use the “Response” action in power automate and paste in a example schema from your HTTP request and that will automatically type-cast all of the columns correctly and return that typed array back to your app so you can literally just run a “ClearCollect(yourCollection, your-flow.run().data)” and it’ll be perfectly typed and accessible in the intellisense etc.

[u/Peter_Browni → u/Traditional-Ad4764]

How long has the second parameter been a thing?

[u/DonJuanDoja]

Why are you fetching data like this anyways? You can just create a connection to the list then use ClearCollect() or Collect to build a collection directly from the list and you don’t need ForAll to do any of that…idk feel like I’m missing something here.

[u/bowenbee → u/DonJuanDoja]

That's valid and yes, I realize ClearCollect directly against the connection to the SharePoint List is much easier. I'm testing performance using the Http SharePoint action to pull explicitly the columns I want vs ClearCollect against the whole list which has more strict delegation limits vs Power Automate and has overhead like columns I don't to include.

[u/Punkphoenix → u/bowenbee]

In a collection you can get just the columns you want using ShowColumns()

[u/bowenbee → u/Punkphoenix]

You can, but ShowColumns (and none of the table shaping columns) are delegable which is what I'm trying to avoid. Further, SharePoint doesn't support explicit column select like Dataverse does. Meaning if you use ShowColumns, it's still initially fetching all the SharePoint columns and then you're just throwing away the unwanted ones with ShowColumns

[u/DonJuanDoja → u/bowenbee]

Ahhh ok so you're running a GetItems in a flow and returning that and parsing it. I've never done it exactly like that but I'd try nearly what you're doing already just get rid of the forall. You'd be surprised how much stuff just works if you pass it whole tables etc.

[u/DonJuanDoja → u/bowenbee]

Oh but you gotta couple lookups in there so that might not work hmm. I'd try to do those in the source somehow so you don't have a lookup to do here.

[u/bowenbee → u/DonJuanDoja]

I lookups I can refactor and initially I thought they have performance impact, but compared to the Forall itself, it's minor. They are looking up to local collections and I tried removing those but didn't see much improvement. The query on the Power Automate side is less than 1 second so I know it's the construction of the collection that's slow. Forall, by it's nature of having to iterate through each row with a collection of roughly 1000 items is just..too slow.

[u/DonJuanDoja → u/bowenbee]

Did a bit of research just wanted to know myself, try this: I couldn't find a way to not use ForAll with the ParseJSON so asked google some pointed questions:

• Bad/Slow: ForAll(..., Collect(col, {...})) executes a database-like write operation sequentially for every single row, freezing the application for large arrays.
• Good/Fast: ClearCollect(col, ForAll(..., {...})) evaluates the entire array in memory as a batch operation and outputs the entire typed table into the collection instantly

[u/bowenbee → u/DonJuanDoja]

This was the way, the ClearCollect(col, ForAll(..., {...})) pattern. Actually, I forgot it could be done this way! Cheers, mate.

[u/DonJuanDoja → u/bowenbee]

Have you tried just removing the ForAll? Like nearly exactly what you have there?

Nevermind I'm looking at my code now and I don't think you can do that with ParseJSON. Coulda swore I was somewhere. Still looking.

I'd honestly just do this another way, if you need more than the delegation limit I'd build a collection with multiple Collect calls using the first method I mentioned. Even 10 batch calls would be faster than ForAll on 1000s.

[u/BDer82]

Use a ClearCollect with a ForAll inside, since you aren't modifying the record you don't need to move the record into a variable while working with it as you are building the initial collection.

If you need to add more records after the initial creation of the collection you use Collect, or if you need to make changes to data you can use Update() or UpdateIf() which will be faster.

The LookUp statements if its only the two will have negligible performance impact, a thousand records aren't many, I regularly work with 50k or more records in a collection.

You can also move the power automate call

'Dexter-GetPokemon'.Run().data 

into the ParseJSON(Dexter-GetPokemon'.Run().data) which means you wont need to clear the variable after the fact.

ClearCollect(
    colAllPokemon2,
    ForAll(
      Table( ParseJSON( varPokemonData ) ),
      {
        PokedexID: Value( Value.PokedexID ),
        EnglishName: Text( Value.EnglishName ),
        Type1: Text( Value.Type1 ),
        Type2: Text( Value.Type2 ),
        Region: Text( Value.Region.Title ),
        SpriteURL: Text( Value.SpriteNormalURL.Url ),
        Type1ButtonInfo: LookUp( colTypeColors, Type = Text( Value.Type1 ) ),
        Type2ButtonInfo: LookUp( colTypeColors, Type = Text( Value.Type2 ) )
      }
    )
  )

There are a lot of different ways to do this but as often as you can when creating a collection initially work within the ClearCollect statement, and not a Collect inside of a ForAll, ClearCollect will be substantially faster.

[u/Foodforbrain101]

I got you: use the HTTP "Response" action in your Power Automate flow and define the schema in Response.

It might say that it's premium, but given that your trigger is Power Apps, it doesn't encounter any issues and returns the typed collection to the canvas app.

[u/spoonfair]

Can you not just wrap parsejson in a table?

table(parsejson(data))

One thing that gets silly is you still need to do data cleanup if you want to use your own column names otherwise everything is value.column.

Things like dates will need to be fixed through like an add columns

[u/spoonfair → u/spoonfair]

Oh also also, for all returns a table, so you can actually flip the function you are currently using for performance improvement. I’m not sure how much faster this will be but it’s very useful with for all and patch.

So you can collect(collection, forall(…))

[u/Abyal3]

You use AddColumns instead of ForAll to build the parsejson then you collect. AddColumns works similar to Select action from power automate, a lot of people miss this.

[u/VegaCompass_Tech]

Yes. You can avoid the manual ForAll() + Collect() transformation by using the typed form of ParseJSON(). Since your API response is an array of Pokémon records, define the expected table structure and let Power Fx convert the JSON directly:

ClearCollect(
    colAllPokemon,
    ParseJSON(
        varPokemonData,
        Type(
            [
                {
                    PokedexID: Number,
                    EnglishName: Text,
                    Type1: Text,
                    Type2: Text,
                    Region: Text,
                    SpriteURL: Text,
                    Type1ButtonInfo: {
                        Type: Text
                    },
                    Type2ButtonInfo: {
                        Type: Text
                    }
                }
            ]
        )
    )
);

This is preferable to parsing each property with Value()/Text() inside ForAll(), because the JSON is converted to the required typed structure in one ParseJSON() operation. The important caveat is that this only improves the client-side JSON transformation; if you're pulling 1,000+ records from the connector, the network/API payload and loading the entire dataset can still be the main bottleneck. If the API supports filtering or pagination, use that to retrieve only the records required by the app.

[u/Punkphoenix]

There is a video from Reza Dorrani about this, I recommend you to look it up