Large Merged Table needs Updating/Exporting

[bw70316]

I have combined three tables using merge and expanded queries to create a massive table that has more rows than microsoft Excel is capable of storing. I need a way to export this table OR ENTER DATA IN PBI MANUALLY so that it can be udpated. I have something similar with a Sale files connected to sharepoint. Data is entered into the excel sheet there and connected to PBI through sharepoint and refreshed. I was hoping to attain a similar feat, but the dataset is too large. Any advice?

[v-cherch-msft]

Hi @bw70316

You may try to use scheduled refresh. Here is the Microsoft document for your reference.

Regards,

Cherie

[bw70316]

Thanks for all the replies. I for some reason felt that updating the source tables wouldn't work, but lo and behold I was able to add new data to the source tables and it worked fine. Sorry for ths confusion. There was no need to export the massive table. 

[Seward12533]

How did you created this merged table? Where is ther soruce data? Did you create in PowerQuery / PowerPivot in Excel?

PowerBI does not really write back but you might check out http://poweronbi.com/power-planner-features/ its the only option I know to do writeback or any kind of data entry within PowerBI.  FLOW might also be able to help with what you need.

Would highly recommend you consider hosting this in a SAS or other DB for all kinds of reasons.

[bw70316]

The source data was three separate tables brought together by ids for companies, countries, and specialties so that each company was laid out in rows as such: Allergan, Albania, Allergy...Alllergan, Albania, Asthma...Allergan, Albania...Allergan, Albania, Audiologist

So if a new user comes in, I can't simply update the specialty and company at the source tables, right?

I merged and expanded these tables on PBI. Should I work on merging/joining these tables in SQL and pushing the data to PBI from there? Thanks in advance for any advice. I also saw a video involving R-coding that can help export to SQL from PBI, but that seems a bit advanced and I was hoping for a better way. 

[Seward12533]

Why not bring the three source tables directly into PowerBI and use either PowerQuery or relationships to merger and/ relate between tre sources? It’s the magic behind PowerBI. It does so much more than make pretty tables, graphs, and visuals.

[bw70316]

Thanks for the reply. Sorry, perhaps I am not being clear, I have used the Power Query to take three separate tables and combine them into one massive table. I want to be able to add new data to this massive, merged table. For a separate project, we currently have an excel file housed in sharepoint connected to PBI. When a new sale comes in, an individual posts to this excel file, refreshes PBI, and pushes to the Dashboard. Unfortunately, this massive table is too large for an excel file. I was able to export the table to a note pad, b/c it was too large for excel, but i'm at a loss with what to do with the exported data. In summation, I am aiming to add data to this new merged table when new sales come through. What is the best way to add information to an existing merged that is too large for export to excel? If I add data to the source table will PBI automatically run the merges/expansions to include the new data?

[Seward12533 · Accepted solution]

If the historical data does not change, can you have a separate file for new additions and then just append in PowerQuery. Or break the large historical file into separate ones. I do this to say add dummy data to a customer lookup table to add potential customers to a bookingbforecast that are net yet in our database.

PowerBI does not really have a limit that I know of size of the tables except by the memory on your machine if your asking it do to all the work. I saw an example recently at the MS BI Summit working with a database of 1 Trillion Rows.