Forum Discussion
Large Merged Table needs Updating/Exporting
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?
- 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.
7 Replies
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.
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.
- 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.
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.
