PowerBI Integration Data to a Collection

Like (1) Share

Report

Posted on 5 Dec 2020 02:44:17 by Ami K

15,665 Super User 2024 Season 1

Hi everyone,

Background:

I have a PowerBI report integrated with a PowerApp.

The PowerBI data source is a SharePoint list. The PowerApp is also connected to the same SharePoint list.

I have a report in PowerBI which has a Table visual. The table displays data from the SharePoint list. When a user filters the Table, the PowerApp tile automatically filters and displays the same records. Users can then make changes to those records in the PowerApp.

See example gif:

Problem:

I also want users to bulk edit the pre-filtered records displayed in the PowerApp (pre-filtered by PowerBI). I have been able to assign PowerAppIntegration.Data to a Collection and used ForAll to get bulk edit working. However the side effect is that when I filter the PowerBI Table, the PowerApp no longer automatically filters - and I suspect this is because I have replaced "PowerBIIntegration.Data" in the Items parameter in my Gallery to the Collection name.

I do not think any workarounds are possible but I wanted to know if anyone has any ideas?

ForAll(

 Filter(

 collectChecked,

 IsChecked

 ),

 Patch(

 DB_MarketFeedback,

 LookUp(

 DB_MarketFeedback,

 CustomerID = Customer

 ),

 {

 Validation_Status: Dropdown_Status.SelectedText.Value,

 Comment: TextInput_Comment.Text

 }

 )

);

ClearCollect(

 collectChecked,

 AddColumns(

 PowerBIIntegration.Data,

 "IsChecked",

 false

 )

)

best regards,

Ami

Categories:

General Questions

I have the same question (0)

All responses (7)

Answers (0)

GauravAgarwal11 27 on 27 Mar 2023 at 10:56:21

Like (1)

Report

Re: PowerBI Integration Data to a Collection

That helps! Thank you for taking time to respond.

Was this reply helpful? Yes No
Ami K 15,665 Super User 2024 Season 1 on 25 Mar 2023 at 04:09:01

Like (1)

Report

Re: PowerBI Integration Data to a Collection

Hi @GauravAgarwal11 - it has been a few years but I think I recall we went with patching each item individually as opposed to patching in bulk, so I cannot confirm if this would have worked with 5k+ items.

The "Customer ID = Customer" part is a standard pattern used for Patching. Customer ID is the column in SharePoint and Customer is the ID of the selected item in the Power App.

Shane Young and Reza Dorrani have a number of tutorials on YouTube explaining this more detail.

Was this reply helpful? Yes No
GauravAgarwal11 27 on 24 Mar 2023 at 20:31:47

Like (1)

Report

Re: PowerBI Integration Data to a Collection

@Amik Thank you for this solution!!
Will this work if the SP list has 5k+ items?
Also, in the lookup(Customer ID = Customer, what exactly are the two parameters? Like Customer ID is the column in Sharepoint and Customer is column name in Power BI?

Was this reply helpful? Yes No
Ami K 15,665 Super User 2024 Season 1 on 06 Dec 2020 at 02:21:47

Like (0)

Report
Re: PowerBI Integration Data to a Collection

Thanks for your thoughts on this
Just realised a better approach would be to forget trying to allow multiple selections for patching, because in my scenario it is still possible to select indivudal records using the save button
linked to each item.

For bulk updates, i can still patch all filered/visible items in the Gallery without needing any Collections (the filtering is controlled by PowerBI).

ForAll( Gallery1.AllItems, Patch( DB_MarketFeedback, LookUp( DB_MarketFeedback, CustomerID = Customer ), { Validation_Status: Dropdown_Status.SelectedText.Value, Comment: TextInput_Comment.Text } ) )

best wishes,
Ami

Was this reply helpful? Yes No
poweractivate 11,078 Most Valuable Professional on 06 Dec 2020 at 00:21:48

Like (1)

Report

Re: PowerBI Integration Data to a Collection

@imranamikhan

Correct, if you cannot change your setup to DirectQuery data source, then the PowerBIIntegration.Refresh() is not supported and you must not use this formula in your case if you keep the same data source. You must use the alternate way instead. In that case, you must note that if you need the bulk edit change to show up right away in the Power BI report from embedded Power App Canvas App while the Power BI dataset is in the Import mode, and you do not wish to simply refresh the dataset manually, or otherwise issue a dataset refresh command, (or otherwise potentially perform some other kind of detailed steps that have to do with a live or near-live Power BI dataset that we will not go into here, and it is not the main subject of your query anyway) - we do not know any other workaround for that.

Also, your thinking is right because as you noticed PowerAppIntegration.Data is a read only and cannot be used to Patch into.

However for your specific issue that is the subject of your query, we think that one probably can be solved as follows:

With regards to the ForAll and Collection, you could attempt to consider to not place the abstracted Collection into the Items parameter. Instead, that Collection built using ForAll can perhaps be instead built inline and dynamically and only when the change is actually being submitted from the save icon you have there in the Canvas App (perhaps in the OnSelect property of the save icon inside the Gallery). Then you can use the read only integration in the Items parameter, but still use the Collection to patch to it. Check if something like this could help.

If you were using Items with Collection because you have concern that it was not possible to fetch the correct corresponding item from the real collection related to the read only collection - note that it should be possible. The primary key should be there, just check that Save Icon Gallery row's attribute and that should be fetchable easily from the real collection - then Patch it. In that kind of way, maybe the ForAll is not even needed then - but you can check it further.

Was this reply helpful? Yes No
imranamikhan 90 on 06 Dec 2020 at 00:09:08

Like (1)

Report

Re: PowerBI Integration Data to a Collection

Thanks @poweractivate

Unfortunately I cannot think of any other method of bulk updating multiple records with one button without creating a Collection to identify the selected records.

If you know of or could direct to me to any other options that would be appreciated. I am currently using the method from @Shanescows:

https://www.youtube.com/watch?v=0xZ4fMJbLvk

As far as I am aware PowerBIIntegration.Refresh() is only supported on a DirectQuery data connection which a SharePoint list does not support.

Was this reply helpful? Yes No
poweractivate 11,078 Most Valuable Professional on 05 Dec 2020 at 23:43:03

Like (1)

Report
Re: PowerBI Integration Data to a Collection

@Amik

Is there any specific reason that PowerAppIntegration.Data cannot be used directly in your case instead rather than abstracting it into a collection?

Also, if any modification is being performed from the Power App itself rather than the report, and you want this to show in the report, you should use following formula from the Power App (place this formula right after the parts of each formula in the Power App that perform any such changes you wish to see in the Power BI report)

PowerBIIntegration.Refresh()

Check if any of the above help.

Was this reply helpful? Yes No