cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DarthTim
Frequent Visitor

Gateway Refresh error indicating missing column

I have a dashboard which is referencing redshift as the source. 

It has been refreshing successfully through PBI desktop and publishing. However, we recently implemented a gateway and it is now unable to refresh due to an error. 

The error received is shown below. After researching online, one of the recommended solutions was to clear the cache. I have done this, but the error is still received. 

The interesting thing though is each time I refresh, it shows a different column as missing. I have probably refreshed 10 times over the past two days while trying to test things out to resolve, and it shows the same error message, but sometimes it refers to a column on table A, while the next time I refresh, it then refers to a column on table B. So far there are 4 columns I have gotten this error message for, each on a different table. 

 

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 column 'split_nbr' of the table wasn't found."}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.Detail","detail":{"type":1,"value":"split_nbr"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"Expression.Error"}}],"exceptionCulprit":1}}} Table: Skill Listing.

 

1) I have cleared the cache, but that does not seem to resolve the problem

2) I have checked the data source and the tables and columns are there, and the spelling as case as referred to them in Power BI matches how it is on redshift. 

3) Looking at the steps for the table mentioned in the error message above, these are the steps with the column mentioned in the error highlighted. I have deleted and recreated steps 3, 4 and 5, but that didnt help resolve the problem. 

 

Step 1: Source 
Step 2: Navigation= bpwfm{[Name="ref_sla_skill_mapping"]}[Data]
Step 3: Removed other columns= Table.SelectColumns(ref_sla_skill_mapping1,{"split_nbr", "split_name"})
Step 4: Sorted rows= Table.Sort(#"Removed Other Columns",{{"split_nbr", Order.Ascending}})
Step 5: Removed duplicates= Table.Distinct(#"Sorted Rows", {"split_nbr"})
Step 6: Removed blank rows= Table.SelectRows(#"Removed Duplicates", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
Step 7: Added custom= Table.AddColumn(#"Removed Blank Rows", "SplitNbrName", each [split_nbr] & " - " & [split_name])

 

4) As a side question, in the steps above, I noticed in step 3, it added a '1' (highlighted in blue) to the end of my table name. I was concerned this was the problem, so I deleted it out, but when I did that, it said it couldnt find the source table. 

 

Does anyone have an idea on how to get around this problem. Its like whack-a-mole, I attend to one column, clear the cache and refresh, and another column pops up. Thanks!

1 ACCEPTED SOLUTION
DarthTim
Frequent Visitor

It looks like I have this figured out. 

It seems the service account used for the gateway did not have access to the specific schema being referenced. So we assigned access and we are now able to refresh through the gateway. 

So the error message we were receiving referring to a missing column was very misleading. I would have expected to get an access denied error message as opposed to a specific column missing. 

So if you get a column not found message when going through a gateway, check if your access is setup correctly. 

View solution in original post

11 REPLIES 11
DarthTim
Frequent Visitor

It looks like I have this figured out. 

It seems the service account used for the gateway did not have access to the specific schema being referenced. So we assigned access and we are now able to refresh through the gateway. 

So the error message we were receiving referring to a missing column was very misleading. I would have expected to get an access denied error message as opposed to a specific column missing. 

So if you get a column not found message when going through a gateway, check if your access is setup correctly. 

lbendlin
Super User
Super User

Every time you make substantial meta data changes in your dataset (or these changes are forced upon you by the data source) you need to re-publish the report to the workspace, and you may also need to also update the app.

I'm sorry, I'm not understanding. 

Currently, to get this refreshed I'm refreshing from the data source using PBI desktop and republishing to the workspace. It works fine when I do that. But when I try and add this to the scheduling in the workspace using the gateway, is when I get these errors. 

Thanks.

you forgot to update the app.

Are you referring to the PBIX file? I'm not sure what updates would be required. 

I am referring to the workspace in the service (after you published the PBIX) and the app that needs updating

 

Thanks. We dont have this published as an app. This is just published as a report. 

In that case I don't know what else to suggest.  Maybe republish the pbix.

Thanks for your help. 

 

Hi, @DarthTim 

Have you tried publishing to a different workspace and reconfiguring the gateway?
If the problem persists, it is recommended to create a support ticket to let MS engineers look into the issue on your side.

 

Best Regards,
Community Support Team _ Eason

No, I have not tried publishing to a different workspace. I will see if I can get access to another workspace. If I dont have any luck, I will try opening a ticket. Thank you. 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors