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?
Solved! Go to Solution.
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.
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?
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.