Forum Discussion

KSL24's avatar
KSL24
Copper Contributor
Jan 22, 2020

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 n...
BI & Data Analysis
excel
Formulas and Functions
power bi
KSL24's avatar
KSL24
Copper Contributor
Jan 26, 2020

SergeiBaklan 

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.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jan 27, 2020

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.

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Feb 02, 2020

    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.

  • KSL24's avatar
    KSL24
    Copper Contributor
    Feb 02, 2020

    SergeiBaklan 

    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.

Resources