Showing results for 
Search instead for 
Did you mean: 
Helper II
Helper II

Best method for connecting Excel or SP List to Power BI to Transform


I am trying to build something, but I don't know if it possible or the best way to accomplish it. Any advice for a workable or good solution would be much appreciated.


Main question

I am trying to automate a process of transforming data. I know about how to do it in Excel (well) and Power BI (less well). However, I want to automate the process more. Previously, I have [automatically] added data to an Excel sheet and I would always need to manually refresh the query (to transform data). I did try and find out if one could automatically refresh that through Flow, but I determined (through research online) that it could not be done - I am less familiar with scripts, but I don't think they worked, either.


My main quesiton: is there a method to automatically transform the data from a Excel or SharePoint List (SP List)?


My Data 

My data takes a somewhat (?) long route with multiple Flows:

  1. I get an email with a CSV attachment.
  2. The attachment is saved into Folder One.
  3. When a new item is added to Folder One, a Flow parses the data and saves it as a JSON in Folder Two.
  4. When a new item is added in Folder Two, a Flow adds all the items to an Excel table.
  5. From here, I would manually refresh the query to transform the data.

I have been experimenting with SP Lists. Instead, or in addition to, step four, I intend to add the data to a SP List.



Along with what I have been doing - or completely different - how can I create a Flow to automatically transform my data with Excel or Power BI and then input the data back into a SP List? 

As I have said, I already know how to Excel transform works. I would prefer a method with Power BI because I am really interested in knowing how that works and using it. However, a good solution or any solution would be much appreciated.


Thank you.

Helper III
Helper III

We all hope that Microsoft is working on this. As so many are looking for this solution. We have a SharePoint list with over 10K line items. I have overcome every challenge in building a power app, and workflows - EXCEPT this one.
I am having to go old school and use Windows Task Scheduler to refresh the excel files - then a flow to send out the attached file. Using a difference in time of day to allow for the data refresh prior to a daily attachment being sent out.  Since this data result is being shared with an external business partner - excel attachment was our only option.
Hope this blog helps someone - until the day when refreshing excel is possible with newer methods

Helpful resources

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Super User 2 - 2022 Congratulations 768x460.png

Welcome Super Users

The Super User program for 2022- Season 2 has kicked off!

Users online (2,706)