Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
uadrian92
Frequent Visitor

Refresh fails on online service but works in desktop version

 
Hey,

 

I cant seem to solve this problem for days, nothing that I try works. I tried to refresh the dataset countless times but always the same error.

 

The error message is an absolute abomination and doesn't help at all. The only sensible information is: "The key didn't match any rows in the table" but it's not really helpful either...

 

Everything works flawlessly in the desktop version but always fails on the online service...

 

Send.

Help.

 

The error message:

 

Last refresh failed: Fri Mar 20 2020 14:50:35 GMT+0100 (közép-európai téli idő)
There was an error when processing the data in the dataset.Hide details

Data source error: {"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"The key didn't match any rows in the table."}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.Key","detail":{"type":1,"value":"[Schema = \"data-warehouse\", Item = \"mortgage\"]"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"Expression.Error"}},{"code":"Microsoft.Data.Mashup.ValueError.Table","detail":{"type":1,"value":"#table({\"Name\", \"Data\", \"Schema\", \"Item\", \"Kind\"}, {})"}}],"exceptionCulprit":1}}} Table: data-warehouse mortgage.
Cluster URI: WABI-WEST-EUROPE-D-PRIMARY-redirect.analysis.windows.net
Activity ID: fcb620b4-3dee-4248-bb70-115751b5d2cc
Request ID: e8c5ff65-6f3f-a840-e688-b70e719293e1
Time: 2020-03-20 13:50:35Z

1 ACCEPTED SOLUTION

Hi,

 

thanks, found the solution but oh my god...

 

Table names on localhost were lowercase (because it automatically converts table names on import to lowercase) while on the "live" db some tables had uppercase name and those failed.

 

So I guess the correct error message would be Table 'xyz.abc' doesn't exist.

 

I admit to my mistakes but come on, Microsoft... this one really is not on me 😄

 

Thanks again tho 🙂

 

 

View solution in original post

12 REPLIES 12
GlenanPP
New Member

Hi Everyone

I had the same problem, the table's names on the db and in the service was equal, so the solution found here didn't work for me.

After many days and hours looking for some solution on the web withouth lucky, finally this work for me:

 

In the service, in the workspace were is located the datset,  in the dataset menu (3 dots) ---> configuration , Gateway and cloud connections --> Assigment --> Add to Gateway

 

In the new window fill all required as it was a new connection. take care with Privacy Level, in my case both was Organizational, desktop and this section.

 

After that refreshed my dataset and woola!! works!!.

 

I hope this solution work for you too.

 

 

Tried your way , but facing the same error.

Devesh790
Frequent Visitor

Are you connecting the database using ODBC Connectors ?

tctrout
Resolver III
Resolver III

Below are items I would walk thru to assist in troubleshooting.  A lot of it depends on your permission level and your companies configuration.

 

If the the data sources are on premise (SQL Server, Network drive etc)

1. The on premise Data Gateway is not created/configured for your data source.  You will need to add it to the Gateway, or have your Gateway Admin add it, assuming it is appropiate for your organization.

 

 2. The data connection exists within the gateway, but the dataset was not mapped to which connection within the Gateway.  

 

 

Hi,

 

thanks, found the solution but oh my god...

 

Table names on localhost were lowercase (because it automatically converts table names on import to lowercase) while on the "live" db some tables had uppercase name and those failed.

 

So I guess the correct error message would be Table 'xyz.abc' doesn't exist.

 

I admit to my mistakes but come on, Microsoft... this one really is not on me 😄

 

Thanks again tho 🙂

 

 

Hello, 

 

I have the same problema but can't fix it 😕  1 question,   how you fixed to problem ?  where did you change the name of table ?  

Anonymous
Not applicable

Hi,

I've been getting the same error for one of my report.
Could you elaborate on how you solved the issue?
Did you just change the table names into lowercase in the database?

Where exactly did you find the "live" database table names?

Anonymous
Not applicable

Can you tell me exactly where you found the discrepencies in the names? I migrated my gateway from a desktop to a server and am now recieving the same error you had. 

 

thanks, 

cj

Hello,

 

the difference was only that on localhost I had my table named "source" but in the live db it was "SOURCE". And while it was perfectly working in the desktop app, it failed to refresh. So make sure the casing matches.

 

One additional thing I found: If you are using mysql views as data source make sure the casing of tables matches there aswell. (Right click on the view, alter view). I found that I had: JOIN `data-warehouse`.`source` `src` ON .... (because I copied my create view query from localhost) instead of JOIN `data-warehouse`.`SOURCE` `src` ON .... And while it seemed to work (I mean I could query this view in Workbench), Power BI didn't seem to find the (lowercase) source table.

 

Not sure if this was any help, let me know if you are still receiving the error.

mpk
Frequent Visitor

Hi , 

 

I am also facing same issue while connecting to a snowflake DB view. Desktop everythigns is woring but failing in online refresh. 

I have removed all transformations in report and it's simple drag and drop report still giving error in refresh. Can't seem to fix it . Please help. 

 

Something went wrong

There was an error when processing the data in the dataset.

Please try again later or contact support. If you contact support, please provide these details.

  • Data source error{"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"The key didn't match any rows in the table."}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.Key","detail":{"type":1,"value":"[Name = \"ITAI_DB\", Kind = \"Database\"]"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"Expression.Error"}}],"exceptionCulprit":1}}} Table: AIP_IBSA_CHNG_MNGMT_REPORT_VEW.
  • Cluster URIWABI-US-NORTH-CENTRAL-B-redirect.analysis.windows.net
  • Activity ID8a90031e-9c47-478d-93e5-e8c005a7270a
  • Request ID61bca180-4da1-4cb1-f444-23ed55c4947a
  • Time2020-08-11 14:54:04Z

hello!

same problem 

refresh fail from scheduled but ok from desktop

canb you help me with the error? i couldt solve it

  • {"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"No se puede convertir el valor \"[Binary]\" al tipo Binary."}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"Expression.Error"}}],"exceptionCulprit":1}}} Table: Hora.
  • URI de clústerWABI-SOUTH-CENTRAL-US-redirect.analysis.windows.n

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors