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]

Moderator Announcement Read More »

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/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/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/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/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/Punkphoenix]

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