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

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 II
Helper II

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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Users online (3,089)