Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.