Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hipau
Frequent Visitor

Automate Power Query Refresh and Import into SQL Server

Hi,

I'm looking for some advice on how to best automate this weekly process:

 

1. Open numerous .xlsx files that use Power Query to connect to Microsoft Dynamics.
          - To elebroate, the .xlsx Power Query uses Source = OData.Feed to connect to Microsoft Dynamics

2. Select Refresh All. Save and close.

3. Open SQL Server Management Studio and use the import wizard to copy data & delete rows in destination table (not append rows).
4. Open the .pbix dashboard, which connect to the Microsoft SQL Server Tables and select Referesh All.

          - It may be possible to streamline the process by having .pbix files point to the .xlsx fles, bypassing SQL Server Management Studio, but there are a number of transformations that are more efficient in SQL Server, which makes this an unfavourable option.

 

Apologies if this is in the wrong section, or if i need to split this up into a few posts. 

Regards, hipau

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

SSIS or Azure Data Factory


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
Greg_Deckler
Super User
Super User

SSIS or Azure Data Factory


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thanks for your response. I'll take a look into SSIS as a starting point. Is there any particular reason you would choose one over the other?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors