Automatically Visualising Calendar/CSV data from Power Automate in Power BI

[pp365]

Hello everyone,

I am hoping someone can advise me on the best solve for this; please keep in mind I am very new to Power BI (although have good experience in the rest of Power Platform).

We have a requirement for a user to analyse data from a Microsoft Exchage ('Outlook') calendar. The user wants to select calendar date ranges/times, capture a .csv file each time they perform the action (they wish to archive the CSV files for later reference), and then visualise the results in a Power BI report or Dashboard. (I am still not sure which is the most appropriate).

To date, we've set up a simple Canvas App in Power Apps to allow the user to select things like the start date and time, and end date and time, of the range of day(s) they want to get data from the calendar. This hands off the time/date strings to a Power Automate Flow, which queries the calendar, returns the data to the Flow, formats the return as CSV and then saves the resulting CSV file in a SharePoint document library. This part of the process is running well and the data is correctly retrieved and stored in the CSV.

Unfortunately I am now completely unsure how to "dynamically" get the data in to Power BI so they can visualise it. By dynamically, I mean, each time they 'press the button' in the Power App to initiate the above process, at the end of the process they should end up looking at a Power BI report/dashboard which is showing them a standardised set of visuals (the same visuals etc each time the process is run). The ideal scenario is the user does not have to download the CSV, upload it anywhere, etc. Additionally, ideally, the user will not see records relating to previous runs of the process but will only see records relating to the 'button press' they just made.

I have somewhat been going 'around in circles' on this. I read about using the Power BI connector in Power Automate, using the 'Add rows to a dataset' action, but then my Dataset didn't show up, someone said it may be because it is not a streaming data set (and a static one), then I found out that is due to be deprecated so 'Real Time Intelligence' in Microsoft Fabric may be needed, but then I couldn't see a way to get the data out to 'Real Time Intelligence' from the Flow.

I am also unsure whether it's best to try and get the records out from the Flow in to Power BI, or, whether Power BI would "prefer" to use the CSV file stored in the SharePoint document library to surface the data to the end user.

I also don't really understand how I can set up my visuals and reports/dashboards in Power BI Desktop, then upload this to the Service, such that I can federate the updating of the source data out to the end user in this scenario. In my limited experience of Power BI I find the nomenclature of Models/Reports/Dashboards and the fact some things have to be done in the desktop App and some things through the web browser to be confusing and I suspect this is not helping! 

I would think that this is not an unusual scenario - IE have an end user update their own data that is already cloud-based, in an automated way - but being a Power BI novice I really can't find the best way to start addressing this.

Any assistance would be very gratefully recieved - thank you - and please keep in mind I never use Power BI and find the structure and terminology quite confusing, so the more detail you are willing to share in any responses the better. Thank you very much for your help which is greatly appreciated 🙂

[lbendlin · Accepted solution]

1. You could use a PowerApp visual inside Power BI to capture the user input, write it into the data storage, and initiate a page rerender (optionally a semantic model or table refresh)

2. Up until recently the report user would not immediately see  the results of their actions reflected back in Power BI as your data source is not accessed in Direct Query mode.  However, it seems that now a semantic model refresh also caused a re-rendering.

3. If you go anywhere near a premium connector (say, a SQL Server data source) all of your PowerApps users will require a PowerApps Premium license.

Consider using Translytical Taskflows and Fabric SQL databases instead.

[pp365]

Hello everyone, thanks again for your suggestions on this topic and apologies for the delayed reply. Thank you @v-echaithra for checking in.

In the end, I used a Power Automate Flow to get the data in to Dataverse, and the plan is to then model the data from there. In the Flow we are able to bulk delete the Dataverse rows which means each time the user requests the data, the previous data is cleared and then refreshed.

It's probably not the most efficient way but it appears to work. Thanks again for your inputs.

[v-echaithra]

Hi @pp365 ,

Thank you for the update and for sharing the workaround that has been effective in your case.

If you need any further assistance, please don't hesitate to reach out. We're always here to help!

Best regards,
Chaithra E.

[v-echaithra]

Hi @pp365 ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[v-echaithra]

Hi @pp365 ,

I just wanted to check if the issue has been resolved on your end, or if you require any further assistance. Please feel free to let us know, we’re happy to help!

Thank you 
Chaithra E.

[pp365]

Hi @lbendlin ,

Many thanks for your reply and taking the time to share some suggesitons.

I had a look at Translytical Taskflows, this seems way too complicated, we do not have any code/SQL experience and as someone new to Power BI this seems out of reach. Also it seems to be in Preview so I can't use it for production.

Moving back to the idea of using a Power App Visual inside Power BI. This seems more feasible and thank you for this idea. I have started some research/design ideas and will try for a PoC using this approach. I'll post back here with any meaningful updates in due course.

I would like to keep the thread open for the time being in case any other community members have suggestion about the general ask, and/or pointers about the 'Power Apps inside Power BI' approach. 

Thanks again!

[v-echaithra · Accepted solution]

Hi @pp365 ,

If you're planning to move ahead with the Power Apps visual inside Power BI approach, that’s a solid direction for your scenario. It lets the user trigger your Flow, generate the CSV, write data into storage, and then have the report automatically re-render with the updated results all from within the same Power BI report. It’s much simpler than building a custom ingestion pipeline, and it fits your use case where the end user initiates the data retrieval themselves.
To help you get started, Microsoft has a step-by-step guide on how to embed a Power Apps visual inside a Power BI report: Embed a new Power App in a Power BI Report. - Power BI | Microsoft Learn

Best Regards,
Chaithra E.

[lbendlin · Accepted solution]

1. You could use a PowerApp visual inside Power BI to capture the user input, write it into the data storage, and initiate a page rerender (optionally a semantic model or table refresh)

2. Up until recently the report user would not immediately see  the results of their actions reflected back in Power BI as your data source is not accessed in Direct Query mode.  However, it seems that now a semantic model refresh also caused a re-rendering.

3. If you go anywhere near a premium connector (say, a SQL Server data source) all of your PowerApps users will require a PowerApps Premium license.

Consider using Translytical Taskflows and Fabric SQL databases instead.