Forum Discussion
Strategy for implementing a menu-style list that captures and saves user selections.
I'd like to create a PowerBI or PowerQuery interface that outputs a list created by a query and then allows users to select a subset of items, which are saved to new table -- or somewhere where they won't be overwritten. See simplified example below.
Would anyone have suggestions for the best way to do this? I'd prefer to use PowerBI because I could add search filters.
In Excel I can imagine these steps ...
- Query #1 generates a list and loads to Column B
- Place a User-Selection table in Column A that's dimensioned to match the number of rows in the query output where users can check a box indicate that an item is selected.
- Place a formula in Column C that references the query output in Column B if Column A is selected.
- Query #2 captures the content of Column B and filters out blank rows.
I'd appreciate any suggestions!
4 Replies
- MFelix
Super User
Hi meagain ,
Using the Power BI you may try and use the Power Automate visualization, that allows to create custom automations based on Power BI data.
Using this you can create/update a file or a list in sharepoint that saves returns the Query2 data.
Another option on Excel is using VBA.
Miguel, thanks for the tip about Power Automate. I'll check it out.
MFelix wrote:Hi meagain ,
Using the Power BI you may try and use the Power Automate visualization, that allows to create custom automations based on Power BI data.
Using this you can create/update a file or a list in sharepoint that saves returns the Query2 data.
So far I haven't had success connecting PowerBI to Power Automate.- Tried using the online version of Flow but I don't seem to have access to sharepoint via either of my MS accounts (O365 at home and Office 2016 at work).
- In PowerBI I tried using the Power Automate visual but that also tries to connect to a MS online account.
- Tried downloading and installing Power Automate desktop, but I can't figure out how to connect that app to a PowerBI or Excel query as a datasource.
- MFelix
Hi meagain ,
Do you have a onedrive account? There is no need to connect to sharepoint this can be done in a series of connections.
Check out if the flow below makes what you need, if yes tyou only need to replicate this on the power automate viasualization.
https://flow.microsoft.com/en-us/galleries/public/templates/4db78650-9144-4150-ae1a-2001d58d2bf9/update-an-excel-table-from-power-bi/ (This uses excel online)
But there can be made adjustment with different flows.
