I'm not sure if I've uncovered a bug or what with a Power BI Data model that I built for our Sales/Finance team. The issue that I'm encountering is that the Power BI Service is unable to refresh the dataset, even though I have the Gateway configured, and the service works for other datasets. The only thing that I can tell that is different about this dataset from the others is that I am merging a query from a SQL table/view and Sharepoint hosted excel file into a table/query.
Basically, after spending hours trying to figure out what was unique, I've narrowed down the issue to the following situation:
Table RepDeptLookup.... is an excel file hosted on a sharepoint site that our Sales manager edits to put in department attributes for his sales team. The query is defined below (I've stripped identifying stuff from it):
let
Source = SharePoint.Files("https://XXXXXXXX.sharepoint.com/Finance/", [ApiVersion = 15]),
#"PowerBI_RepDeptLookup xlsx_https://XXXXXXXX sharepoint com/Finance/Shared Documents/" = Source{[Name="PowerBI_RepDeptLookup.xlsx",#"Folder Path"="https://XXXXXXXX.sharepoint.com/Finance/Shared Documents/"]}[Content],
#"Imported Excel" = Excel.Workbook(#"PowerBI_RepDeptLookup xlsx_https://XXXXXXXX sharepoint com/Finance/Shared Documents/"),
RepDept_Table = #"Imported Excel"{[Item="RepDept",Kind="Table"]}[Data]
in
RepDept_Table
Then there is table PBI_Users, which comes from a SQL view I created, that has a list of our Users. I merge this list with the sharepoint table above (RepDeptLookup) so that I can get the departments and notes that our manager has for his sales team added to the PBI_User table (which is highlighted in Red below):
let
Source = Sql.Database("SQLServer", "CRM_DB"),
dbo_PBI_Users = Source{[Schema="dbo",Item="PBI_Users"]}[Data],
#"Filtered Rows (remove null employment status)" = Table.SelectRows(dbo_PBI_Users, each ([Employment Status] <> null)),
#"Merged Queries" = Table.NestedJoin(#"Filtered Rows (remove null employment status)",{"SystemUserID"},RepDeptLookup,{"CRMGUID"},"RepDeptLookup",JoinKind.LeftOuter),
#"Expanded RepDeptLookup" = Table.ExpandTableColumn(#"Merged Queries", "RepDeptLookup", {"Dept", "Notes"}, {"Lookup_Dept", "Lookup_Notes"})in
#"Expanded RepDeptLookup"
Unfortunately, when I try to publish the dataset to the Power BI Service and configure it to automatically refresh, I get the following error:
We reached the data gateway, but the gateway can't access the on-premises data source.
But... If I remove the highlighted "red" lines from the PBI_User query and republish, I am able to successfully refresh the dataset via the power bi service.
And in both dataset scenarios, regardless if I use a merged query or not, I am able to refresh from the Power BI Desktop without issues.
SO MY QUESTION IS, how do I fix this issue? I want to be able to combine/merge my queries together regardless of the source, and I thought that's what Power BI allowed, but based on my testing, I can not do this. Also, can these error messages be any less helpful? I spent my whole day trying to figure out what was unique about this dataset to cause this issue, and I think I've done a good job given the very limited feedback that power bi error messages give.
Any help or suggestion or workaround would be greatly appreciated!
