search

jeroenterheerdt / dutchdatadude

Code samples as published on blog
http://www.dutchdatadude.com
4 stars 3 forks source link

query.sql: The table has no visible columns and cannot be queried. in power query #1

Open jullimullins opened 2 years ago

jullimullins commented 2 years ago

DataSource.Error: The table has no visible columns and cannot be queried. Details: states_powerbi

If column state_numeric is removed from the SQL query then data is accessible in PQ

mnrgrrt commented 11 months ago

Hi Jeroen, thanks for explaining how to get to the HA data. I am absolutly not familiar with this kind of data but I would like to experiment withe the posibillities of Powerbi. Almost everything works but when I run the sql query I get an error. Can you explain what I do wrong?

Thanks, Gerry

Fout SQL-query: Kopiëren Documentatie

CREATE VIEW states_powerbi as select a.state_id ,a.entity_id ,a.state ,a.attributes ,JSON_EXTRACT(a.attributes, '$.unit_of_measurement') as unit_of_measurement ,a.last_changed ,a.last_updated ,a.created ,CASE WHEN a.isnumeric = 1 THEN a.state_numeric else NULL END as state_numeric from ( select state_id, entity_id, state, attributes, last_changed, last_updated, created, IsNumeric(state) as isnumeric, CAST(state as float) as state_numeric from states ) a; MySQL meldt: Documentatie

1054 - Onbekende kolom 'created' in field list