Comma separated list to 2 column collection

[iwonder]

Thank you for taking the time to read my question.

I'm wondering if this is the most efficient / best way to split the below string into a 2 column collection

ForAll(
Split("Monitor,2;Mouse,1;Keyboard,3",";").Result As items1 ,Collect(colPeripherals,{PeripheralType:First(Split(items1.Result,",")).Result,PeripheralCount:Last(Split(items1.Result,",")).Result}))

Monitor,2;Mouse,1;Keyboard,1

becomes

PeripheralType | PeripheralCount

Monitor            | 2

Mouse              | 1

Keyboard          | 1

Is there a way to do it with just commas or is this better?

Thanks!

[Base2Rob]

@iwonder - I'm not sure whether what I have below is more efficient, but having written CSV parsers in other languages, I might suggest this type of approach:

/*  This approach will also work for a collection of input strings.
    You would just wrap another for loop around that collection as the first step */

// Store input string
UpdateContext({varInputString: "Monitor,2;Mouse,1;Keyboard,1"});

// Build collection of rows by splitting on row delimeter ';'
Collect(
    colRows,
    Split(varInputString, ";").Result
);

// Iterate over row collection, split on column delimeter ','and output to collection
ForAll(
    colRows,
    Collect(
        colOutputTable,
        {
            PeripheralType: First(Split(ThisRecord.Result, ",")).Result,
            PeripheralCount: Last(Split(ThisRecord.Result, ",")).Result
        }
    )
)

Let me know if this helps and good luck!

[RandyHayes · Accepted solution]

@iwonder 

So, your ForAll is backward in the formula you have.  AND, this is a good example of the power of using ForAll as intended.

The purpose of a ForAll is to return a Table of records.  It is not a ForLoop like you would use in a development language.

The syntax of the ForAll is essentially two parameters.  The first is the table of records that you want to iterate over and the second is the record that you want to return for each iteration.

It is important to understand that ForAll will do the above action regardless of what you intend it to do.  So, if you intend to try and make it a ForLoop, it will still do the above and will create a Table of records based on the second parameter.

Your second parameter is a collect statement - this will return, for each iteration of the table, a modified table that the collection represents.  Since you are not providing a schema for your records, the ForAll will stuff this all into a column called Value.  So, your result of the ForAll will be a table with a single column called Value that will have many iterations (rows from source) of the entire table of results from each Collect statement.

Now, in your scenario, this is probably not a huge overhead on performance and memory, but this ForAll as a ForLoop is often used incorrectly and PowerApps will have all of the overhead and impact on performance to generate large tables of results...that, based on the formula, are then discarded.

So, your formula based on your example should be:

ForAll(
   // Parameter 1 - source table
   Split("Monitor,2;Mouse,1;Keyboard,3",";"),

   // Paremeter 2 - record schema
   {PeripheralType:First(Split(Result,",")).Result,
    PeripheralCount:Last(Split(Result,",")).Result
   }
)

The above formula will return a table with records based on the provided schema (columns) for each row of the source table (the Split of the text).

In terms of performance (and typing), this can be further simplified to:

ForAll(
   // Parameter 1 - source table
   Split("Monitor,2;Mouse,1;Keyboard,3",";"),

   // Paremeter 2 - record schema
   With({_res: Split(Result,",")},
      {PeripheralType:First(_res).Result,
       PeripheralCount:Last(_res).Result
      }
   )
)

This will keep PowerApps from constantly performing the Split function for each column of the record.

Now, the above returns a table...what you do with it after that is up to you.

You can use the above on an Items property of any control.

Or, if you want to have this available as a table in the app, then set a variable to the above results.

Set(glbSplits, <theAboveFormula>)

Or, if you need the ability to add, remove or change the resultant records, assign it to a collection.

ClearCollect(colSplits, <theAboveFormula>)

I hope this is helpful for you.

[iwonder]

Thank you both for your replies.

Thank you @RandyHayes ... I'll catch on to the ForAll() one day 🙂

Best wishes to both of you and your families this holiday season

[RandyHayes]

@iwonder 

Yes, and I promise I will be doing a video on the use of ForAll (properly) one day too 🙂

It's in the works, so, fingers crossed, it will be out soon.

Best wishes to you and your family as well over the holidays.  Hopefully you get some rest time too!

[Base2Rob]

@RandyHayes - Having developed in other languages for many years, it took me a minute to realize that ForAll is NOT the PowerFx version of ForEach 😉

[RandyHayes]

@Base2Rob 

Yes, it is a much different function.  I find that ForAll is the number 1 data shaping function, that packs a lot of power.  Using it like a ForEach really cripples it and causes one to write so many more formulas to try and compensate for it.

Remember...PowerApps is just Excel on steroids!