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

Historical Table

Dear, is there any way to connect to a table, bring the data and then disconnect, leaving the data as well as it is available in the model to use and that table does not participate in synchronization?

The thing is that I have a historical table that will no longer have movements, so I don't want to keep the connection to the data source because it's not needed.

I need to import the data from that table into the power bi and that's it.

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

Hi @adciudad ,

 

In Power Query, right click on the query that you want to keep as-is and deselect "Include in report refresh". This should prevent the data in it being refreshed when you hit "Refresh" on the ribbon:

adciudad.PNG

 

Note: I believe that you still need to maintain the connection to the data source though.

 

*EDIT* This may cause you some issues if you publish to the Power BI service as this does not prevent the Service from trying to refresh your dataset. I think the only sure-fire way to maintain a completely static dataset/table is to get your connected table how you want it in Power Query, then go to to the tiny table icon at the top-left of your data, select "Copy entire table", then go to Enter Data and paste. However, even this will only allow a maximum of 3,000 cells to be pasted. If you have more, best to paste this table out into an Excel file and connect back to it knowing it will never be changed.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

8 REPLIES 8
v-diye-msft
Community Support
Community Support

Hi @adciudad 

 

What's the datasource you'd like to disable the connection?

Did you try to import the data source and then clear the permission for the data source? 

9.PNG

Then you are not able to refresh the data neither keep live-conenction with it.

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

It SQL server.

If I remove permissions it doesn't work, it asks me for access to the base, so that it's marked as not including in data refresh...

adciudad
Helper II
Helper II

Thank you for all the answers, I already did what you mention, but what I want is to be able to disconnect the data source, since as it is a history is not always available.

The idea is to import it once and don't bother you anymore...

Anonymous
Not applicable

As mentioned if you disable in report refresh then it won't bother the data anymore. 

depending on how much data you could also save the data as an excel document and then copy and paste it into your Ovid file using enter data. The down side is that only gives you 3000 data points. 

Yes, I disabled it, but the data source remains configured, therefore it is validated when upgrading, I want to incorporate data one time into the model

parry2k
Super User
Super User

@adciudad you can right-click on the table in power query, and uncheck "Include in refresh"

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

amitchandak
Super User
Super User

BA_Pete
Super User
Super User

Hi @adciudad ,

 

In Power Query, right click on the query that you want to keep as-is and deselect "Include in report refresh". This should prevent the data in it being refreshed when you hit "Refresh" on the ribbon:

adciudad.PNG

 

Note: I believe that you still need to maintain the connection to the data source though.

 

*EDIT* This may cause you some issues if you publish to the Power BI service as this does not prevent the Service from trying to refresh your dataset. I think the only sure-fire way to maintain a completely static dataset/table is to get your connected table how you want it in Power Query, then go to to the tiny table icon at the top-left of your data, select "Copy entire table", then go to Enter Data and paste. However, even this will only allow a maximum of 3,000 cells to be pasted. If you have more, best to paste this table out into an Excel file and connect back to it knowing it will never be changed.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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.