PowerApps/Power Automate: Send List Data To PowerApps And Convert To Collection

 Hello Friends,

Welcome back with another post on PowerApps with Power Automate. In real time scenarios, we often face the problem where we have to get some filtered data from data source and to display in PowerApps Gallery.

One way it could be we can use delegation but sometimes it doesn't meet our requirements. In those scenarios, we have to move on towards Power Automate, which could return us a data result and we can show it in gallery.

It sounds good but practically it has challenges like, we have no option to return table as output to PowerApps. We can return data of type Text, Yes/No, File, Email, Number, Date.

You may be thinking that we can parse the result set in JSON and return it as text. 😊

Yes, you can but the returned content is of type text and it will be treated as plain string. So, What Next?

There is a trick through which we will send data to PowerApps and will convert into collection. Let's see how-

1. Suppose I have a list called Quote where I am managing daily display quotes. It has primarily 2 columns-
1. DisplayDate
2. Quote
2. 
3. I need to fetch these quotes from list and show in a gallery in PowerApps. I am using Power Automate.
4. So, let's start with making flow in Power Automate. Open the Power Automate platform and click on + Create and choose Instant cloud flow. Give it a lovely name and choose the trigger type as PowerApps.
5. 
   
   
6. Click on Create to get the flow created.
7. Now click on + New step and add an action Get Items (SharePoint). Choose the SharePoint site in Site Address box. Then choose respective list in List Name box.
8. 
   
   
9. From this data, we need only DisplayDate and the Quote to be sent to PowerApps. So, we will initialize a variable and concatenate this data using two separate delimiters-
1. ## for separating the data (DisplayDate##Quote)
2. $$ for separating the rows (DisplayDate##Quote$$DisplayDate##Quote)
10. For this use Initialize variable and then use Apply to each action.
11. 
    
    
12. In Apply to each action, use Append to string variable to append the concatenated value to the variable.
13. 
14. This way, we have concatenated all rows. But the problem is that an additional instance of row delimiter ($$) is concatenated at the end of this string. So we have to remove it. For that, we will use the substring function.
15. Choose a Compose action and assign the substring value to it.
16.  
17. Now, the last part of this flow is to return this data back to PowerApps. For this, add an action Respond to a PowerApp of flow.
18. Choose Output as Text.
1. Enter the title- "ResponseResult".
2. Enter a value to respond- vrConcatenatedData
19. 
    
    
20. That's all in Power Automate. You may test it manually-
21. 
22. Now we will create PowerApps.
23. Create a Canvas PowerApp.
24. 
25. Add a button-
1. Name: btnGetQuotes
2. Display Text: Get Quotes
26. 
    
    
27. Now we will use the OnSelect property of this button to apply our functionality.
28. First of all, we will fetch the data from Power Automate to a variable. For this choose the OnSelect property of button and click in the function box then click on Action >> Power Automate >> GetQuotesForPowerApps.
29. It will add this flow in function box of OnSelect property.
30. 
31. Complete this function as below-

32. Set(
        varResponseResult,
        GetQuotesForPowerApps.Run().responseresult
    );
    

33. 
34. Now we will split this data based on Row Delimiter ($$).

35. ClearCollect(
        collResponseResultSplitRowDelimited,
        Split(
            varResponseResult,
            "$$"
        )
    );
    

36. 
    
    
37. Now execute the button to get the collection loaded.
38. 
39. As we can see, the collection is loaded with each row. Now, we will split each row with data delimiter (##).

40. ForAll(
        collResponseResultSplitRowDelimited.Result,
        Collect(
            collResponseResultFinal,
            {
                ID: First(
                    Split(
                        Result,
                        "##"
                    )
                ).Result,
                Title: Last(
                    Split(
                        Result,
                        "##"
                    )
                ).Result
            }
        )
    );
    

41. 
    
    
    
    
42. Thus, complete code is-

43. Set(
        varResponseResult,
        GetQuotesForPowerApps.Run().responseresult
    );
    ClearCollect(
        collResponseResultSplitRowDelimited,
        Split(
            varResponseResult,
            "$$"
        )
    );
    ClearCollect(
        collResponseResultFinal,
        []
    );
    ForAll(
        collResponseResultSplitRowDelimited.Result,
        Collect(
            collResponseResultFinal,
            {
                ID: First(
                    Split(
                        Result,
                        "##"
                    )
                ).Result,
                Title: Last(
                    Split(
                        Result,
                        "##"
                    )
                ).Result
            }
        )
    );
    

44. The output is-
45. 
46. Lastly, I had bind this collection to a gallery.
47. 
48. This way, we can achieve the desired functionality.
49. UPDATE:
50. In case, if you need to send more than 2 columns from Power Automate then we need to make some changes in PowerApps to split using data delimiter. Let's suppose, we need to send the SNo column also. Then, first, we will make changes in Power Automate as (we are using the Title column because the internal name of this column is Title) below-
51. 
52. 

53. concat(items('Apply_to_each')?['DisplayDate'],'##',items('Apply_to_each')?['Quote'],'##',items('Apply_to_each')?['Title'],'$$')
    

54. 
55. Now, we know that the sequence of data in each row is as-
1. DisplayDate
2. Quote
3. SNo
56. But we want to display in below format-

57. SNo

58. DisplayDate

59. Quote

60. Therefore, we will make changes in the last ForAll function which is written on OnSelect property of btnGetQuotes button

61. ForAll(
        collResponseResultSplitRowDelimited.Result,
        Collect(
            collResponseResultFinal,
            {
                SNo: Last(
                    Split(
                        Result,
                        "##"
                    )
                ).Result,
                DisplayDate: First(
                    Split(
                        Result,
                        "##"
                    )
                ).Result,
                Title: Last(
                    FirstN(
                        Split(
                            Result,
                            "##"
                        ),
                        2
                    )
                ).Result
            }
        )
    );
    

62. As the SNo was appearing at last of the item row, hence we fetched it using Last function.
63. DisplayDate was appearing at first of the item row, hence we fetched it using First function.
64. Quote is now appearing in the middle of the item row at position 2 hence we have used Last with FirstN function. First we fetched First 2 records. It will return DisplayDate & Quote, then we applied Last function, so as the Quote is at the last of this result set, we will get Quote.
65. 
66. 
67. This way we can send as much as data from Power Automate and get it displayed in PowerApps.

With this, I am concluding this post.

Happy Coding !!!

Will see you again with some new topics.

Stay Safe !

Stay Healthy !