Covert lookup result into a collection of 2 columns

[Sujimon]

Hi,

Is there a way to convert a Lookup result of single row record into a multi row collection with 2 columns, col1 with column names of Lookup record and col2 with field item..the number of columns in the Lookup will change over time so it needs to be dynamic. This will be simlar to transpose in excel.

Thanks

Suji

[poweractivate]

@Sujimon 

Check if the below threads help you:

Transpose Data Collection 

How to transpose a sharepoint list in a data table in powerapps 

[Sujimon]

Hi,

The first link worked perfectly for me but I would like to understand how it works. The code is too comples for me.

Thanks

Suji

[poweractivate · Accepted solution]

Clear(Target_Collection);
ForAll(

    // create table of records in JSON format and substitute out the unneeded Chars
    Split(
        Substitute(
            Substitute(
                Substitute(
                    Substitute(
                        Substitute(
                            JSON(
                                Orig_Collection,
                                JSONFormat.IgnoreBinaryData
                            ),
                            "[",
                            ""
                        ),
                        "{",
                        ""
                    ),
                    "}",
                    ""
                ),
                "]",
                ""
            ),
            Char(34),
            ""
        ),
        ","
    ),
    
    // Transpose data
    Collect(
        Target_Collection,
        {
            Question: Left(
                ThisRecord.Result,
                Find(
                    ":",
                    ThisRecord.Result
                ) - 1
            ),
            Response: Mid(
                ThisRecord.Result,
                Find(
                    ":",
                    ThisRecord.Result
                ) + 1
            )
        }
    )
)

from Transpose Data Collection 

So how it works is:

    Split(
        Substitute(
            Substitute(
                Substitute(
                    Substitute(
                        Substitute(
                            JSON(
                                Orig_Collection,
                                JSONFormat.IgnoreBinaryData
                            ),
                            "[",
                            ""
                        ),
                        "{",
                        ""
                    ),
                    "}",
                    ""
                ),
                "]",
                ""
            ),
            Char(34),
            ""
        ),
        ","
    )

This part above is a trick to cause the ForAll to be able to iterate over a Table.

Basically it turns it into a JSON, then it removes characters, then it splits by a delimiter Char(34) which is quotation mark I believe.

If you want to inspect it, just take the above part and put it into a Gallery separately for example, then hover over the formula bar and see what's inside it.

And now

// Transpose data
    Collect(
        Target_Collection,
        {
            Question: Left(
                ThisRecord.Result,
                Find(
                    ":",
                    ThisRecord.Result
                ) - 1
            ),
            Response: Mid(
                ThisRecord.Result,
                Find(
                    ":",
                    ThisRecord.Result
                ) + 1
            )
        }
    )

This part above is the one you may need to customize to your specific Record and data structure.

That art of the formula is the other argument of ForAll, which means, that it essentially takes the stuff from the Table mentioned above, iterates over it, and puts it in a Collection Target_Collection .

This is where you need to adjust it.

For example, to change Question and Response to the names of your 2 columns respectively.

I did not test any of the formulas, so I would need to test it to tell you any more than the generics above.

For now, I do not know the following:

1. I don't know if it works for more than 2 columns. You could try this

// pseudocode - may not work
    Collect(
        Target_Collection,
        {
            Question: Left(
                ThisRecord.Result,
                Find(
                    ":",
                    ThisRecord.Result
                ) - 1
            ),
            Response: Mid(
                ThisRecord.Result,
                Find(
                    ":",
                    ThisRecord.Result
                ) + 1
            ),
            Column3: Mid(
                ThisRecord.Result,
                Find(
                    ":",
                    ThisRecord.Result
                ) + 2
            )
        }
    )

But the above may or may not work and is an untested guess. If it does work, I guess you're lucky. If it does not work, I either gave the wrong formula, which is probably the case (and in that event, you may need to figure it out yourself), or the top part with the Substitute needs to be changed too, and for that, I would need to investigate the formula over more time. Right now, I don't think I have enough time to investigate this formula with that level of detail that might be needed to give you a complete solution for your requirement to have multiple columns (so in that event, you may need to figure it out as well).

What I recommend for now is that if the solution works for you for two columns, and you can just change the names of the columns Question and Response, you could go with that for now if it works "perfectly" for you, since it seems like you were happy with it so far. 

In case the person who gave that original formula might be around, I'll mention @Anonymous here in case they would like to offer more details in case you want more columns.

I also would welcome someone else who wants to check on this with more detail, to reply further with more details on multiple columns, or maybe to even walk through each part of the formula for you in more detail than I did. For example I might be interested what @RandyHayes thinks about this particular scenario, if they have the time to check on it, and is interested to check on it.

See if it helps @Sujimon 

[RandyHayes · Accepted solution]

@poweractivate 

Well....you were curious of my response 😂😉

I would say your approach is very programmatic!  If PowerApps was more code development I would say that fits the bill.  BUT, PowerApps does work in a much different way and most things you want to do are very quick and easy.

The major issue to start was that the ForAll is used like a For Loop.  It is a function that creates a table of records.  Although it can be "forced" to act like a for loop to some degree, it is best (especially in terms of performance) to use it as designed.

SO...your entire formula in a more PowerApp'ian way would be this:

ClearCollect(Target_Collection,
    ForAll(
        MatchAll(JSON(Orig_Collectionm IgnoreBinaryData), """(?<nvalue>[^""]+)"": *""?(?<jvalue>[^(""|\n)]+)""?").SubMatches,
        {Question:First(SubMatches).Value, Response:Last(SubMatches).Value}
    )
)

This produces the same results with much less formula writing!  And...it uses the ForAll as a table creating function that it is.

To explain - at the inner core, the collection is converted to JSON.  That is "fed" to the MatchAll function that returns a table based on the RegularExpression matching provided.

The table from the MatchAll is fed to the ForAll where it is used to iterate over the table rows and then produce records with a Question and Response column using the associated values from the matched row of the source table.

And...ForAll returns a table, that is then fed to ClearCollect where it is duplicated into a collection.

Normally I would advise against the collection in general as it is not needed in this case, but since the sample was based on it...

As for the original question by @Sujimon 

The problem with this approach is that you will not get away from altering your App if your columns change.  In other words, nothing you have so far will allow for "dynamic".

That is because PowerApps needs to know the schema of your source when it is designed.  It "locks" that in from that point forward until you refresh the source in your app designer.

So, while the above will give you what you want with one record schema, it will not dynamically change as the source changes.

Hopefully this is helpful for all.

[RandyHayes]

@Sujimon 

Additional note...keep in mind that last part that I mentioned about how PowerApps must know the specific of the datasource and schemas at design time.  

This is why it cannot allow reference to data sources by name - as you asked in your other post.

[poweractivate]

@RandyHayes 

I checked your response and I found the approach much better.

I just found the formula in another thread actually, and then I was curious how you would approach it, and actually I like your approach much better for this scenario. Thank you for taking the time to share it! 🙂

[RandyHayes]

@poweractivate 

Yeah...way too much "programming" type approaches out there.  That's not PowerApps!  It's much simpler if you go the "PowerApps way"!