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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
kenyonca
Frequent Visitor

Auto Refresh with multiple data sources

I am attempting to have my Power BI dashboard pull in data from multiple data sources and update the online dashboard autmoatically each night.

 

My first data source, SmartSheet, is updated daily and the revised data is scheduled to be pulled in each night.

 

The second data source is the real issue. Right now it is set up on an Excel sheet (approx. 38,000 rows).

I have tried to save it through a shared OneDrive. This however needs to have an online connection to ensure the gateway connection is successful. Unfortunately this isnt an option as I do not plan on having this computer always online for the 3 year duration of the project.

This second data source will rarely if ever change as it is just a long alignment approx 38km with associated longtitude and latitudes and a few columns of meta-data related to the stationing.

I attempted to use the "Enter Data" option but this kept on saying that there was too much data to be copied to the clipboard.

As well I tried to dump this into SmartSheet but it has a limit of 5000 rows.

 

Does anyone know another way to store these ~38,000 rows of data easily online so that PowerBI can automatically pull them in too without having to create a Gateway that would require my computer to be online for these connections.

 

Thanks!

3 REPLIES 3
Greg_Deckler
Super User
Super User

Why not just spin up an Azure SQL DB and load it there? Would be pretty cost effective. You could consider a SharePoint Online list but at 38,000 rows that might prove to be a problem. Azure Table storage might be even more economical.


Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Yes that is an option we have thought about.

 

Is there no way to just either save the 38,000 lines in PowerBI with the Enter Data.

That or to get Power BI web version to not pull from that particular data source on the auto-refresh? But only pull from the SmartSheet data? The desktop version lets you choose to not include certain data sets when you trigger the refresh but the web version doesnt seem to have this capability.

@kenyonca,

 

Is there no way to just either save the 38,000 lines in PowerBI with the Enter Data.
Currently, we cannot do it by using Enter Data.

 

You could store your excel fiel to your Power BI group's OneDrive for Business. You can continue updating files you store in OneDrive for Business, and those updates are automatically reflected in the Power BI reports and dashboards based on the files. Please refer to the link below.
https://powerbi.microsoft.com/en-us/documentation/powerbi-service-connect-to-files-on-your-groups-on...

Regards,

Charlie Liao

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors