when selecting table from Azure Synapse dedicated SQL pool

Hi ShivaPr,

The path and credential checks above are the right starting point. A few extra things I would look at, because error 16562 says "location does not exist or it is used by another process", and that second half is often the real cause.

First, confirm the object chain. Power BI selects dbo.BD, but the error names dbo.BDRatesHourlyRate, so dbo.BD is most likely a view over that external table. Run this in Synapse Studio with the same login Power BI uses:

-- what does dbo.BD depend on?
SELECT OBJECT_NAME(referencing_id) AS referencing_object,
       referenced_entity_name
FROM sys.sql_expression_dependencies
WHERE referencing_id = OBJECT_ID('dbo.BD');

-- where does the external table point?
SELECT t.name, t.location, ds.name AS data_source,
       ds.location AS data_source_location
FROM sys.external_tables t
JOIN sys.external_data_sources ds
  ON t.data_source_id = ds.data_source_id
WHERE t.name = 'BDRatesHourlyRate';

-- can this login read it directly?
SELECT TOP 10 * FROM dbo.BDRatesHourlyRate;

Then check these less obvious causes:

a load (ADF / pipeline / Spark) overwrites the folder
  while Power BI queries it -> files briefly missing
LOCATION folder is empty, or only holds files starting
  with _ or . (treated as hidden by PolyBase)
storage firewall blocks Synapse -> "Allow trusted
  Microsoft services" off, or the workspace managed
  identity lacks Storage Blob Data Reader

The hidden-file behavior and the LOCATION rules are described in the CREATE EXTERNAL TABLE documentation.

If the timing explanation fits, the most robust fix for Power BI is not to read the external table directly at all. I would land the data in an internal table after each load and point Power BI there:

-- run after each load completes
IF OBJECT_ID('dbo.BDRatesHourlyRate_curated') IS NOT NULL
    DROP TABLE dbo.BDRatesHourlyRate_curated;

CREATE TABLE dbo.BDRatesHourlyRate_curated
WITH (DISTRIBUTION = ROUND_ROBIN,
      CLUSTERED COLUMNSTORE INDEX)
AS
SELECT * FROM dbo.BDRatesHourlyRate;

That removes the storage dependency from the Power BI refresh, avoids reads while files are being overwritten, and is usually much faster in a dedicated SQL pool.

So my overall approach would be:

confirm the view -> external table chain
-> rule out concurrent writes and empty/hidden folders
-> verify firewall and managed identity access
-> serve Power BI from an internal table

That way Power BI reads a stable table, and the external table stays a staging layer only.

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.