How to create a collection from SQL data grouped together in one field?

[AlexHobbs]

Hello all,

I'm attempting to break up a string that I've brought into PowerApps from SQL as collection. The string contains data surrounded by quotes. For example, the string below has three useful values to me; 2, 510, and 516.

a:3:{i:0;s:1:"2";i:1;s:3:"510";i:2;s:3:"516";}

Is there a way to create a collection from the data that is surrounded by quotes?

I would like to create a collection like the following:

Record1      2

Record1      510

Record1      516

Record2      2

Record3      2

.

.

.

I'm thinking that this may need to be done in SQL. My PowerApp is utilizing some Sharepoint lists if there is a way to collect the data in sharepoint.

Thanks,

[Drrickryp · Accepted solution]

Hi @AlexHobbs,

You would use the Split function to do that.  https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-split.  The syntax is 

Split( Text, Separator )

• Text - Required. Text to split.
• Separator - Required. Separator to use in splitting the string. Can be zero, one, or more characters.

The example in the documentation is 

Split( "Apples, Oranges, Bananas", "," )

Splits the different fruits apart, based on the comma separator. The split is performed based on only the comma and not the space after it, resulting in a space at the front of " Oranges" and " Bananas".

The result is a table of substrings that you can use as input where ever a table can be used, for example in the Items property of a gallery or dropdown control.

If you are going to use your data for a lookup table and do not require subsequent modifications or addition of new data, I would suggest exporting it to Excel, using PowerQuery to shape the table and importing into PowerApps as static data.

[AlexHobbs]

Thanks Drrickryp. I was definitely overcomplicating a solution. I used the Char (34) to split the data.

Any thoughts on the best way to remove the excess data from my result table?

[Drrickryp]

Hi @AlexHobbs,

You cannot change the underlying structure of a datasource in PowerApps, but you can create a collection from the data and reshape the collection using AddColumns(), RenameColumns(), DropColumns() and ShowColumns() functions, see table-shaping

[CarlosFigueira]

Hello,

This is a good scenario for the new functions that were just added in this week's release that deal with regular expressions. By using the MatchAll function, you can extract the data between the quotes, with an expression like this one:

learCollect(
    Coll,
    { record: 1, scope_id: "a:3:{i:0;s:1:""2"";i:1;s:3:""510"";i:2;s:3:""516"";}" },
    { record: 2, scope_id: "a:1:{i:0;s:1:""2"";}" },
    { record: 3, scope_id: "a:1:{i:0;s:1:""2"";}" },
    { record: 4, scope_id: "a:2:{i:0;s:1:""3"";i:1;s:1:""5"";}" },
    { record: 5, scope_id: "a:2:{i:0;s:1:""4"";i:1;s:2:""27"";}" }
);
ClearCollect(
    Coll2,
    AddColumns(
        Coll,
        "BetweenQuotes",
        MatchAll(
            scope_id,
            "\""(?<betweenquotes>[^\""]+)\"""
        ).betweenquotes
    )
)

The attached app shows how you would use that expression in a gallery, for example.

[AlexHobbs]

Thanks Carlos. 

This is much easier that the work around that I came up with. I'll check out the new functions and incorporate them into my PowerApp.