Forum Discussion
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!
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.
11 Replies
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.
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.
