Forum Discussion

pay_2019's avatar
pay_2019
Icon for Advocate I rankAdvocate I
4 years ago
Solved

Using a linked Excel file in Access and having trouble with scheduled refresh in Power BI

I have a linked excel document held in my Access database which I use to create Power BI reports. The Access database also pulls in data on other ODBC links with third party software. When I refresh ...
Need Help
  • pay_2019's avatar
    pay_2019
    4 years ago

    I have resolved it by using the table in Access and creating a schedule update to make the table using AutoSQL. Not ideal but it now allows me to set the schedule refresh up and is working.

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi pay_2019 ,

What is your connection method? Can you refresh the dataset on the Power BI Service on demand? If not, what is the reason for the error? Can you please try to download the pbix file and open it on the Power BI Desktop.

If it is possible, please provide more screenshots with the refresh errors.

 

Please refer to the topic to see if it helps you.

Refresh excel power pivot linked to SSAS on premise 

 

Best Regards

Community Support Team _ Polly

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

  • pay_2019's avatar
    pay_2019
    Icon for Advocate I rankAdvocate I
    4 years ago

    See below the error message if I try to refresh the dataset in place:

     

    Underlying error code: -2147467259
    Underlying error message: The key didn't match any rows in the table.
    DM_ErrorDetailNameCode_UnderlyingHResult: -2147467259
    Microsoft.Data.Mashup.ValueError.Key: [Schema = "", Item = "Standing cost report"]
    Microsoft.Data.Mashup.ValueError.Reason: Expression.Error
    Microsoft.Data.Mashup.ValueError.Table: #table({"Name", "Data", "Schema", "Item", "Kind"}, {})
    Cluster URI: WABI-UK-SOUTH-B-PRIMARY-redirect.analysis.windows.net
    Activity ID: d2f5945e-3e55-4843-8b93-d24628148eb2
    Request ID: 91284bcd-ed30-5baa-c8da-d6f445e41e41
    Time: 2022-05-10 08:30:46Z

     

    It does not relate to the Access table listed above called "Standing cost report" as the below error can also happen, "VOR Summary" uses a linked excel table in the query in Access which I think is causing the issue.

     

    Underlying error code: -2147467259
    Underlying error message: The key didn't match any rows in the table.
    DM_ErrorDetailNameCode_UnderlyingHResult: -2147467259
    Microsoft.Data.Mashup.ValueError.Key: [Schema = "", Item = "VOR Summary"]
    Microsoft.Data.Mashup.ValueError.Reason: Expression.Error
    Microsoft.Data.Mashup.ValueError.Table: #table({"Name", "Data", "Schema", "Item", "Kind"}, {})
    Cluster URI: WABI-UK-SOUTH-B-PRIMARY-redirect.analysis.windows.net
    Activity ID: d2f5945e-3e55-4843-8b93-d24628148eb2
    Request ID: 6d0f82b1-3aa2-2326-bfd8-504746ac2ff7
    Time: 2022-05-10 08:32:29Z

     

    In some cases on Power BI Desktop the refresh will work without any issue, so unsure why this could be the case? I am wondering whether it would be better to build the queries directly in Power Query within Power BI and link in the excel document for this.

     

    Any suggestions would be appreciated.

    Thanks

    Paul.