Update fails with empty SPO List

[Anonymous]

Hallo,

we have one Power BI Report with about 10 SPO Lists.

If one of the 10 SPO Lists is empty (no records) every Update action fails with Error: The column 'ID' of the table wasn´t found.

Also the scheduled refresh fails with Power BI Services!!

Please remove this Bug or provide a workaround!!!

thx.

Johannes

[v-danhe-msft]

Hi @Anonymous,

Based on my research, you could refer to below link that may have the same issue with you:

https://community.powerbi.com/t5/Desktop/Expression-Error-column-quot-x-quot-of-the-table-wasn-t-found/td-p/455417

https://community.powerbi.com/t5/Desktop/Expression-Error-The-column-lt-ColumnTitle-gt-of-the-table-wasn/td-p/71961

Hope it could help you.

Regards,

Daniel He

[Anonymous]

Hello,

Sorry your provided Links are totally other problems! I don´t use special expressions or Dropbox....

Just use a simple SPO List with data and create a simple report.

Then delete all rows of the tabel in SPO.

Refresh fails

lg.

Johannes

[v-danhe-msft]

Hi @Anonymous,

Based on my test, I could reproduce your problem and you could try to delete the #"Renamed Columns" step in query editor to fix your problem.

See below picture:

Hope it could help you.

Regards,

Daniel He

[Anonymous]

Hi Daniel,

thx for your reply.

I have a Query like this:

let
Quelle = SharePoint.Tables("https://dentalwerk.sharepoint.com/sites/cct_spo", [ApiVersion = 15]),
#"cf1adcc7-7e6b-4225-9f61-42a9c3c6590d" = Quelle{[Id="cf1adcc7-7e6b-4225-9f61-42a9c3c6590d"]}[Items],
#"Umbenannte Spalten" = Table.RenameColumns(#"cf1adcc7-7e6b-4225-9f61-42a9c3c6590d",{{"ID", "ID.1"}}),
#"Andere entfernte Spalten" = Table.SelectColumns(#"Umbenannte Spalten",{"CCT_Pricedate", "CCT_RRP_Price", "CCT_RLP_Price", "CCT_Promo_Price", "CCT_Street_Price", "CCT_Country_LU", "P_E_HWS_LU"}),
#"Geänderter Typ" = Table.TransformColumnTypes(#"Andere entfernte Spalten",{{"CCT_Pricedate", type date}}),
#"Geänderter Typ mit Gebietsschema" = Table.TransformColumnTypes(#"Geänderter Typ", {{"CCT_RRP_Price", Currency.Type}, {"CCT_RLP_Price", Currency.Type}, {"CCT_Promo_Price", Currency.Type}, {"CCT_Street_Price", Currency.Type}}, "de-AT"),
#"Erweiterte CCT_Country_LU" = Table.ExpandRecordColumn(#"Geänderter Typ mit Gebietsschema", "CCT_Country_LU", {"Title"}, {"CCT_Country_LU.Title"}),
#"Erweiterte P_E_HWS_LU" = Table.ExpandRecordColumn(#"Erweiterte CCT_Country_LU", "P_E_HWS_LU", {"Title", "Company_LU", "Developmentplattform"}, {"P_E_HWS_LU.Title", "P_E_HWS_LU.Company_LU", "P_E_HWS_LU.Developmentplattform"}),
#"Erweiterte P_E_HWS_LU.Company_LU" = Table.ExpandRecordColumn(#"Erweiterte P_E_HWS_LU", "P_E_HWS_LU.Company_LU", {"Title"}, {"P_E_HWS_LU.Company_LU.Title"}),
#"Erweiterte P_E_HWS_LU.Developmentplattform" = Table.ExpandRecordColumn(#"Erweiterte P_E_HWS_LU.Company_LU", "P_E_HWS_LU.Developmentplattform", {"Title", "GrothIndex"}, {"P_E_HWS_LU.Developmentplattform.Title", "P_E_HWS_LU.Developmentplattform.GrothIndex"})
in
#"Erweiterte P_E_HWS_LU.Developmentplattform"

Can you tell me how to do your workaround with this query? I always get error after change!

thx.

Johannes

[v-danhe-msft · Accepted solution]

Hi @Anonymous,

You could try to modify your query editor with below code:

let
Quelle = SharePoint.Tables("https://dentalwerk.sharepoint.com/sites/cct_spo", [ApiVersion = 15]),
#"cf1adcc7-7e6b-4225-9f61-42a9c3c6590d" = Quelle{[Id="cf1adcc7-7e6b-4225-9f61-42a9c3c6590d"]}[Items]
in
#"cf1adcc7-7e6b-4225-9f61-42a9c3c6590d"

Hope it could help for you.

Regards,

Daniel He

[F_Bastiat]

How does that help? That's just the standard query when importing any SPO list, it would fail just the same.