Forum Discussion
Excel - Office 365 (Desktop)
Hello.
I have an excel workbook with multiple sheets and tables. Each of them is loaded on a data model, uploaded on a Power BI Report (on Desktop) and published on my Workspace (Power BI).
Right now, this tables are being feed manually directly on excel, and when data refreshes, I can see updated info directly in Power BI. I've been trying to look for a way to connect this tables to another worksheet, so another team member, can update some of the information (in secondary workbook), lets say 1/4 of the columns in both worksheets (not all columns/rows because it the file has sensitive data), and from there it can do all the calculations and update the information on real time (on the master workbook)
Is there any way to link a part of a table to a another table in another workbook, so it can be automatically updated when the secondary workbook has new data (rows)?
I've also tried protecting the sensitive columns, but when protected, it wont allow the table to expand for the new inserted rows.
Another important issue, is that the master file can't be opened for Power BI to be able to update the information (there's new information constantly), so the ideal scenario (in case that its possible) would be to have all the information that needs to be entered manually in the secondary file, and all the formulas and calculations in the master file.
Any other suggestions?
Thanks in advance for your help.
9 Replies
Did you consider Power Query as an option to collect all information from secondary file(s) and return result into the master file for reporting?
I did imported the files with power query, and checked that it does refreshes the data from the secondary files (upon request) when inserted new information, but the master files does not update the table.
If you mean scheduled refresh in Power BI or by Microsoft Flow, it updates only the data model and tables in master file will be updated if they are generated from data model (Pivot Table or reversed linked Table in the sheet with EVALUATE in behind). In this case Power Query on secondary file shall update data model in master file.
