Excel - Office 365 (Desktop)

[KSL24]

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.

[#2]

@KSL24 

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?

[#3]

@Sergei Baklan

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.

[#4]

@KSL24 

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.

[#5]

@Sergei Baklan 

Hello Sergei.

I was definitely looking at it the wrong way. I reworked and rearranged all of the workbooks. I now have 2 secondary files, that are connected to the master file through queries, and one master file that is the one connected to Power BI. I previously had in the master file, several additional worksheets that were reading the data in the queries, and from there I did the formulas, which is why it wouldn't expand the table when new data was added. I now calculate the formulas directly in the worksheet that was created by the Power Query, so now when data is added in the secondary file, so it does in the master file. I also added the automatic refresh to the queries in the master file, to the updated every 30 min.

Now, I'm only missing for the master file to be updated when I make an on-demand refresh to the Power BI report/dashboard, without opening the master file and clicking refresh.

Is there any way to to this?

Thanks.

[#6]

@KSL24 

Power BI refresh, on-demand or scheduled one, updates only data model and what is based on it, not the tables returned by Power Query. You need present results taken from data model to be automatically updated.

[#7]

@Sergei Baklan 

To see if I understood, you're saying that I need the Power BI to be directly connected to the data (secondary files) and not to the master file I created with the additional data and formulas., right? But for Power BI to update and refresh the data, the file (Excel) can't be opened. How will I manage this without a Master File?

Thanks.

[#8]

@KSL24 

Not exactly. Let say you have master file placed on Sharepoint / Onederive and connected to Power BI workspace. Power Query in master file updates information from secondary files (doesn't matter where they are) and return information into the tables in master file. Let assume as well quires return data into the master file data model as well.

In this case Power BI refresh won't update returned by quires tables in the sheet and will updated data model only. To work with tables, don't use tables returned by quires, use queries as connections only. Instead return tables (so called reverse linked tables) into the sheet from data model as explained here https://www.sqlbi.com/articles/linkback-tables-in-powerpivot-for-excel-2013/ and build your formulas based on these tables.

In such case above tables in master file shall be updated by Power BI refresh.

[#9]

@Sergei Baklan 

Hello Sergei. Still no luck. I tried what you last suggested. 

This is what I have or did by step:

• 2 secondary files (for data input) + 1 master file connected to Power BI
• All the files are on OneDrive
• Secondary files imported to master file through queries
• I created new tables from from the existing connections for each query, in a separate worksheet 
• In the new table connections, I added the columns that make the calculations
• Both the new sheets (that now appear as connections), and the queries are set to automatically refresh every 15 min
• I added index columns to the queries, to create a relationship between queries (imported) and connections (connected from the queries as tables)
• All the Power BI reports, are connected and using as queries the new connections created.

So far, unless I open the master file and click on refresh, none of the queries/tables/connections will update.

[#10]

@KSL24 

Let me repeat on sample to be sure we understand the process same way. Let take source file with simple table

Query it from master file

let
    Source = SharePoint.Files(pSiteToTestRefresh, [ApiVersion = 15]),
    ImportExcel = Excel.Workbook(
        Source{[Name = "PBI Refresh Source.xlsx"]}[Content]
    ){[Name = "tblA"]}[Data]
in
    ImportExcel

as connection only with loading data to data model

Next, open Existing Connections and open any table in connections. To illustrate, I created dummy table in master file and selected it to open connection, but that doesn't matter which table to take

Click Open and land connection to the table in the sheet, right click on it and select Edit DAX for Table

Here select DAX and return table from data model with new calculated column

You may add calculated columns and measures using PowerPivot as well.

Let create connection to master file in Power BI, it shall appear both in Workbboks and Datasets sections

Workbook within Power BI looks like

to check how refresh works let change 13 on 33 in source file table, Without opening master file let Refresh now in Power BI

In a while, after refresh is finished we may see that in Power BI the file is updated

If open master file we see the same, it is updated

Scheduled refresh works exactly the same way.