cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
J_Taylor
Helper II
Helper II

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

Hi, 

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.

 

Question

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.

1 REPLY 1
DublinOH_User
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

Announcements
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)