How to split SharePoint string delimited column value and add to collection?

[Anonymous]

Hey there, I got a string delimited SP column date value. (See attached pic) In PowerApps, I am trying to load this value and split it into row values. 

For example lets say in the date col of my list I have this value - 9/08/2022;10/08/2022;11/08/2022.

I want to create a collection in Powerapps and display the above column value in different rows :

Date

-----

row 1 - 9/08/2022

row 2 - 10/08/2022

row 3 - 11/08/2022

Whats the best way I can go about doing this? I tried using Split function but its not working as its a table record and not String.

Thanks in advance

[poweractivate]

@Anonymous 

Try it like this:

Sample Source_collection:

Formula:

NOTE: I am using semicolons as the string delimiter of the sample date string data, to be close to your original example. However, please beware that in my formula, it assumes the "comma" (,) as the delimiter, because the comma is my setting in Power Apps - if you are using Power Apps with semicolon as the delimiter for everything, including for the formulas (such as in another region) and in case you have a different formula delimiter like semicolons, and different formula line ending delimiters, you may have to modify the below formula appropriately for your scenario, region, and use case.

Clear(Destination_collection);
Clear(Intermediate_collection);

ClearCollect(Source_collection,{DateString:"2022/9/1;2022/9/2;2022/9/3"},{DateString:"2022/9/4;2022/9/5;2022/9/6"});

ForAll(
      Source_collection As DateStringRecord
     ,Collect(Intermediate_collection,Split(DateStringRecord.DateString,";"))
);

ClearCollect
(
    Destination_collection
    ,RenameColumns(Intermediate_collection,"Result","Date")
);

If the above gives formula errors (other than because I used commas in my formula and your setting is semicolons, etc. - i.e. if it says something like Destination_collection is not a recognized name as the error):

1. Temporarily change the first two lines to

ClearCollect(Destination_collection,Blank());
ClearCollect(Intermediate_collection,Blank());

2. Trigger the formula to run somehow. 

3. Change the first two lines back to

Clear(Destination_collection);
Clear(Intermediate_collection);

You may also choose to just use the "temporary" ClearCollect lines instead, but if you do, there may appear to be a blank "Value" column in the Collection preview of Destination_collection and Intermediate_collection, whereas with this way where the first two lines are just "Clear", this blank "Value" column is not present in Destination_collection and Intermediate_collection.

Collections result:

NOTE: There are 5 items instead of 6 shown, because only a maximum of the first 5 items of a Collection show in the previews under File -> Collections.

There are actually 6 items there, to see all of them, add a Gallery to Screen1, and for Items, use Destination_collection - all 6 items should be there.

Working Sample msapp

I have attached a working sample msapp file app34.msapp if you would like to import a full, working example yourself for your convenience.

To use the sample msapp attached, follow these steps:

1) Download the msapp file attached to this post (it's at the bottom of this post), to Desktop or a folder of your choice, by clicking on it from this post.

2) Create a new, blank Power App Canvas App

3) Go to File -> Open -> Browse

4 ) Navigate to location of .msapp file from Step 1, select it, and press the "Open" button.

5 ) The working example msapp file should load

6 ) Toggle between Screen2 and Screen1 once to initialize all the collections.
7 ) You can check File -> Collections to see the Collections preview

Check if it helps @Anonymous 

[Anonymous]

Hey @poweractivate , thanks for the reply and the answer given. I have tried it out and it works! Thank you. 

I got one follow-up question, how can I modify the above solution to include multiple columns from the SharePoint list?  So from:

Date                                                        Employee

9/08/2022;10/08/2022;11/08/2022 |       John;Mark;Troy

To

Date                               Employee

-------------------          ----------

row 1 - 9/08/2022          John

row 2 - 10/08/2022        Mark

row 3 - 11/08/2022        Troy

[poweractivate · Accepted solution]

@Anonymous 

Great question!
I have a solution for that below.

I believe this solution is also much improved from the solution I gave first, there is not even an Intermediate_collection anymore, you can check it:

Test sample collections 

Date	Employee
9/08/2022;10/08/2022;11/08/2022	John;Mark;Troy
12/08/2022;01/08/2023	Mike;Jack

(by the way, I made this solution to support not only semicolon delimited Date and the second column of semicolon delimited Employee, but also, more than one row as well, in case you need that too)

Results in a test Gallery:

Formula:

The main formula, OnVisible of Screen1, is:

NOTE: This formula does not validate if the number of semicolon-delimited elements in both columns are the same, and this can cause unexpected results. Read on further for a version of this formula that does validate if the number of elements are the same!

Clear(Destination_collection);

ClearCollect(Source_collection,{Date:"9/08/2022;10/08/2022;11/08/2022 ",Employee:"John;Mark;Troy"},{Date:"12/08/2022;01/08/2023",Employee:"Mike;Jack"});

Clear(Outer_Temp_count);
Clear(Temp_count);

ForAll
(
 ///////    
 ForAll
 (
    Source_collection As SourceData
    ,With
    (  
        {
            IndividualDates:Split(SourceData.Date,";")
            ,IndividualEmployees:Split(SourceData.Employee,";")
        }
        ,Collect(Outer_Temp_count,CountRows(Temp_count));ForAll
        (
            IndividualDates As IndividualDate
            ,Collect(Temp_count,0);
            {
                 Date    :IndividualDate.Result
                ,Employee:Last(FirstN(IndividualEmployees,CountRows(Temp_count)-Last(Outer_Temp_count).Value)).Result
                //,Counts:CountRows(Temp_count)-Last(Outer_Temp_count).Value
            }
        )
    )

    
 ) As Intermediate
 ,Collect(Destination_collection,Intermediate.Value)
 ///////  
);

NOTE: The solution may give unexpected results if the number of delimited elements in Date differs from the number of delimited elements for Employee. For now, the presumption is both should be the same. 

Formula that validates the number of semicolon-delimited elements:
Here below is another version of the formula, where it just skips the whole row and shows a red notification error on the screen, if the lengths of the semicolon separated elements of Date and Employee end up not matching on that row.

This is the version of the formula that is actually inside of the attached sample app:

//this version checks to see if the number of elements in each column are the same, and if not, it skips the row and shows an error notification!

Clear(Destination_collection);

ClearCollect(Source_collection,{Date:"9/08/2022;10/08/2022;11/08/2022",Employee:"John;Mark;Troy"},{Date:"12/08/2022;01/08/2023",Employee:"Mike;Jack"});

Clear(Outer_Temp_count);
Clear(Temp_count);

ForAll
(
 ///////    
 ForAll
 (
    Source_collection As SourceData
    ,With
    (  
        {
            IndividualDates:Split(SourceData.Date,";")
            ,IndividualEmployees:Split(SourceData.Employee,";")
        }
        ,With
         (
         {RowCounts:Distinct([CountRows(IndividualDates),CountRows(IndividualEmployees)],ThisRecord).Result}        
           ,
            If
            (
             CountRows(RowCounts) > 1
            ,Notify("The number of delimited elements must match in all columns",Error);Blank()
            ,Collect(Outer_Temp_count,CountRows(Temp_count));ForAll
             (
                IndividualDates As IndividualDate
                ,Collect(Temp_count,0);
                {
                     Date    
                       :IndividualDate.Result
                    ,Employee
                       :Last(FirstN(IndividualEmployees,CountRows(Temp_count)-Last(Outer_Temp_count).Value)).Result
                    //,Counts:CountRows(Temp_count)-Last(Outer_Temp_count).Value
                }
            )
           )
           
         )
    )

    
 ) As Intermediate
 ,Collect(Destination_collection,Intermediate.Value)
 ///////  
);

Working Sample msapp

I have attached a working sample msapp file app34b.msapp if you would like to import a full, working example yourself for your convenience.

To use the sample msapp attached, follow these steps:

1) Download the msapp file attached to this post (it's at the bottom of this post), to Desktop or a folder of your choice, by clicking on it from this post.

2) Create a new, blank Power App Canvas App

3) Go to File -> Open -> Browse

4 ) Navigate to location of .msapp file from Step 1, select it, and press the "Open" button.

5 ) The working example msapp file should load

6 ) Toggle between Screen2 and Screen1 once to initialize all the collections, if needed.
7 ) You can check File -> Collections to see the Collections preview
8 ) You can also check the test Gallery for the results

Check if it helps @Anonymous 

[poweractivate]

@Anonymous 

To add further columns, carefully modify these parts, using the pattern WHATEVER as below - continuing to add similar lines for additional columns, for the following parts:

//pseudocode
            IndividualDates:Split(SourceData.Date,";")
            ,IndividualEmployees:Split(SourceData.Employee,";")
             .....
            ,IndividualWHATEVER:Split(SourceData.WHATEVER,";")
             .....

.....

//pseudocode
{RowCounts:Distinct([CountRows(IndividualDates),CountRows(IndividualEmployees),CountRows(IndividualWHATEVER)],ThisRecord).Result}  

......

//pseudocode
                     Date    
                       :IndividualDate.Result
                    ,Employee
                       :Last(FirstN(IndividualEmployees,CountRows(Temp_count)-Last(Outer_Temp_count).Value)).Result
                    ,WHATEVER
                       :Last(FirstN(IndividualWHATEVER,CountRows(Temp_count)-Last(Outer_Temp_count).Value)).Result

See if this helps as well @Anonymous 

[Anonymous]

Thank you so much @poweractivate . Tested it out and it works amazing. Thank you for your detailed write ups and suggestions. New to PowerApps and coding in general so it was really helpful!