Invalid object name when merging two queries from different SQL databases

[dusdau]

Have searched around and can't find a reason for this.  I'm simply trying to merge (left join) two queries from different SQL databases on the same server and keep getting this error.  One of the sources is a view and another is a table.  I am doing a few transformations and then just trying to do a left join.  I've done this many times and never received this error so not sure what is different here.

It seems to be looking for the SQL view dbo.vw_PBI_2YearsRollingSales in the wrong database.  It is in a database called ERP_Data but when trying to expand after the merge I get this message.

DataSource.Error: Microsoft SQL: Invalid object name 'dbo.vw_PBI_2YearsRollingSales'.
Details:
DataSourceKind=SQL
DataSourcePath=cordcdw\cordcdw;CRM_Data_Production
Message=Invalid object name 'dbo.vw_PBI_2YearsRollingSales'.
Number=208
Class=16

Any ideas?

Thanks,

Dustin

[v-piga-msft]

Hi @dusdau ,

By my test with the latest version of Power BI Deskop, I cannot reproduce your scenario.

Please check if you have installed the latest version of power bi desktop 2.70.5494.761 firstly.

In addition, if you open a new pbix and connect the data source, then do the same transform will you have this issue?

---

It seems to be looking for the SQL view dbo.vw_PBI_2YearsRollingSales in the wrong database.  It is in a database called ERP_Data but when trying to expand after the merge I get this message.

DataSource.Error: Microsoft SQL: Invalid object name 'dbo.vw_PBI_2YearsRollingSales'.
Details:
DataSourceKind=SQL
DataSourcePath=cordcdw\cordcdw;CRM_Data_Production
Message=Invalid object name 'dbo.vw_PBI_2YearsRollingSales'.
Number=208
Class=16

---

Do you mean that you do not have PBI_2YearsRollingSales in your database? What is your view name?

Best  Regards,

Cherry

[dusdau]

Hi Cherry, thanks for the help.  I have tried this same operation in a new file just to make sure nothing was corrupt in the original or something.  Here is the detail:

Here is the connection to the view in ERP_Data:

let
Source = Sql.Database("cordcdw\cordcdw", "ERP_Data"),
dbo_vw_PBI_2YearsRollingSales = Source{[Schema="dbo",Item="vw_PBI_2YearsRollingSales"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(dbo_vw_PBI_2YearsRollingSales,{"Customer ID", "Last 12 Sales Local", "Prior 12 Sales Local"}),
#"Grouped Rows" = Table.Group(#"Removed Other Columns", {"Customer ID"}, {{"Last 12 Sales", each List.Sum([Last 12 Sales Local]), type number}, {"Prior 12 Sales", each List.Sum([Prior 12 Sales Local]), type number}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "New Customer", each if [Prior 12 Sales] > 0 then 0 else 1),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"New Customer", type logical}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([New Customer] = true))
in
#"Filtered Rows"

And here is the connection to the table in CRM_Data_Production:

let
Source = Sql.Database("cordcdw\cordcdw", "CRM_Data_Production"),
dbo_account = Source{[Schema="dbo",Item="account"]}[Data],
#"Filtered Rows" = Table.SelectRows(dbo_account, each ([SCRIBE_DELETEDON] = null) and ([statuscode] = 1) and ([accountnumber] <> null)),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"address1_city", "pyro_customerservicemanagername", "pyro_tabengineername", "pyro_regionidname", "pyro_regionalmanagername", "customertypecode_displayname", "pyro_marketidname", "createdon", "pyro_generalmanagername", "pyro_countrymanagername", "pyro_empengineername", "pyro_countryidname", "pyro_pyroteksicidname", "pyro_snifengineername", "pyro_globalgroupname", "name", "owneridname", "pyro_metaullicsengineername", "accountnumber", "pyro_customerranking_displayname", "accountid", "pyro_salesmanagername", "pyro_customerservicename", "pyro_isomagengineername"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"createdon", type date}}),
#"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"accountnumber", "name"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"accountnumber", "Account Number"}, {"name", "Account Name"}, {"pyro_globalgroupname", "Global Group"}, {"pyro_customerranking_displayname", "Customer Ranking"}, {"address1_city", "City"}, {"pyro_countryidname", "Country"}, {"pyro_marketidname", "Market"}, {"pyro_pyroteksicidname", "Pyrotek SIC Code"}, {"pyro_salesmanagername", "Sales Manager"}, {"pyro_countrymanagername", "Country Manager"}, {"pyro_generalmanagername", "General Manager"}, {"pyro_regionidname", "Region"}, {"pyro_regionalmanagername", "Regional Manager"}, {"pyro_snifengineername", "SNIF Engineer"}, {"pyro_empengineername", "EMP Engineer"}, {"pyro_tabengineername", "TAB Engineer"}, {"pyro_customerservicemanagername", "Customer Service Manager"}, {"pyro_customerservicename", "Customer Service"}, {"owneridname", "Relationship Manager"}, {"customertypecode_displayname", "Account Type"}, {"accountid", "Account ID"}, {"createdon", "Created On"}, {"pyro_isomagengineername", "ISOMAG Engineer"}, {"pyro_metaullicsengineername", "Metaullics Engineer"}}),
#"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"Account Number"}, #"New Customers", {"Customer ID"}, "New Customers", JoinKind.LeftOuter),
#"Expanded New Customers" = Table.ExpandTableColumn(#"Merged Queries", "New Customers", {"New Customer"}, {"New Customer"})
in
#"Expanded New Customers"

The following error occurs on the “Expanded New Customers” line

DataSource.Error: Microsoft SQL: Invalid object name 'dbo.vw_PBI_2YearsRollingSales'.
Details:
DataSourceKind=SQL
DataSourcePath=cordcdw\cordcdw;CRM_Data_Production
Message=Invalid object name 'dbo.vw_PBI_2YearsRollingSales'.
Number=208
Class=16