Avatar billede micaud Mester
12. april 2022 - 21:43 Der er 6 kommentarer

Optimering af JSON data inden det hentes ned

Hej.

Jeg ved ikke om det er rette kategori.

Jeg vil hente el-priser ind i PowerBi/Excel, og her benyttes Json, som jeg ingen forstand har på.

Koden er:

let
    Kilde = Json.Document(Web.Contents("https://api.energidataservice.dk/datastore_search?" & "resource_id=elspotprices&limit=100")),
    #"Konverteret til tabel" = Table.FromRecords({Kilde}),
    #"Udvidet result" = Table.ExpandRecordColumn(#"Konverteret til tabel", "result", {"records", "_links"}, {"result.records", "result._links"}),
    #"Udvidet result._links" = Table.ExpandRecordColumn(#"Udvidet result", "result._links", {"start", "next"}, {"result._links.start", "result._links.next"}),
    #"Udvidet result.records" = Table.ExpandListColumn(#"Udvidet result._links", "result.records"),
    #"Udvidet result.records1" = Table.ExpandRecordColumn(#"Udvidet result.records", "result.records", {"_id", "HourUTC", "HourDK", "PriceArea", "SpotPriceDKK"}, {"_id", "HourUTC", "HourDK", "PriceArea", "SpotPriceDKK"}),
    #"Filtrerede rækker2" = Table.SelectRows(#"Udvidet result.records1", each ([PriceArea] = "DK2")),
    #"Sorterede rækker1" = Table.Sort(#"Filtrerede rækker2",{{"_id", Order.Ascending}}),
    #"Fjernede andre kolonner" = Table.SelectColumns(#"Sorterede rækker1",{"_id", "HourUTC", "HourDK", "PriceArea", "SpotPriceDKK"}),
    #"Sorterede rækker" = Table.Sort(#"Fjernede andre kolonner",{{"HourDK", Order.Descending}}),
    #"Ændret type1" = Table.TransformColumnTypes(#"Sorterede rækker",{{"HourUTC", type datetime}, {"HourDK", type datetime}})
in
    #"Ændret type1"

Kilden:

Kilde = Json.Document(Web.Contents("https://api.energidataservice.dk/datastore_search?" & "resource_id=elspotprices&limit=100")),

Denne er problemet, da den kun henter de først 100 rækker "resource_id=elspotprices&limit=100")

Databasen indeholder 3000000+ rækker, men jeg skal kun bruge de rækker, hvor "PriceArea = DK2", og jeg kunne også godt nøjes med de rækker hvor "HourUTC >= 01-01-2020"

Men hvordan får jeg det filter ind allerede i kilden (ligesom "limit=100")??

Kan jeg det, så laves filter ude i weben, og så kan jeg vel hente langt hurtigere, da jeg så skal hente langt færre rækker ned.

What to do - håber en kan hjælpe, eller guide mig over i den rigtig kategori.

Tak.
Avatar billede Henrik Hansen Forsker
13. april 2022 - 15:39 #1
Prøv denne URL:

https://api.energidataservice.dk/datastore_search_sql?sql=SELECT * FROM "elspotprices" WHERE "PriceArea" = 'DK2' AND "HourUTC" > timestamp'2020-01-01 00:00' ORDER BY "HourUTC" ASC limit 100

Forklaring står her:
https://www.energidataservice.dk/guides/api-guides
Avatar billede micaud Mester
13. april 2022 - 19:14 #2
Hej.

Tak for input, men det ser ud til at være en SQL query.

Jeg har ikke en SQL, men blot et regneark, hvor jeg bruger power query (kan også bruge PowerBi Desktop), hvor jeg så vidt jeg kan se skal benytte "json.document" for at hente data ind i Power Query (i Excel).
Avatar billede micaud Mester
13. april 2022 - 19:20 #3
Måske er du på rette spor faktisk, men jeg ved ikke, hvordan jeg så skal "expande" alle records i Excel, men jeg fik adgang via Excel ved indsætte din URL

Har du selv prøvet at indsætte data med kilde web i Excel og taste din url, så kan du se, hvad jeg mener :-)
Avatar billede Henrik Hansen Forsker
14. april 2022 - 14:34 #4
Da "links" ikke er en del af SQL-query datasættet, har jeg pillet det ud af din kode.
Ved ikke om det virker, da jeg ikke har adgang til Excel lige nu, men da de forskellige værdier i JSON'ens "records" er det samme (og hedder det samme), så er der en chance. Alt det med sortering er vist heller ikke nødvendigt, da det bliver gjort i SQL'en.

let
    Kilde = Json.Document(Web.Contents("https://api.energidataservice.dk/datastore_search_sql?sql=SELECT * FROM 'elspotprices' WHERE 'PriceArea' = 'DK2' AND 'HourUTC' > timestamp'2020-01-01 00:00' ORDER BY 'HourUTC' ASC limit 100")),
    #"Konverteret til tabel" = Table.FromRecords({Kilde}),
    #"Udvidet result" = Table.ExpandRecordColumn(#"Konverteret til tabel", "result", {"records"}, {"result.records"}),
    #"Udvidet result.records" = Table.ExpandRecordColumn(#"Udvidet result.records", "result.records", {"_id", "HourUTC", "HourDK", "PriceArea", "SpotPriceDKK"}, {"_id", "HourUTC", "HourDK", "PriceArea", "SpotPriceDKK"}),
    #"Filtrerede rækker2" = Table.SelectRows(#"Udvidet result.records1", each ([PriceArea] = "DK2")),
    #"Sorterede rækker1" = Table.Sort(#"Filtrerede rækker2",{{"_id", Order.Ascending}}),
    #"Fjernede andre kolonner" = Table.SelectColumns(#"Sorterede rækker1",{"_id", "HourUTC", "HourDK", "PriceArea", "SpotPriceDKK"}),
    #"Sorterede rækker" = Table.Sort(#"Fjernede andre kolonner",{{"HourDK", Order.Descending}}),
    #"Ændret type1" = Table.TransformColumnTypes(#"Sorterede rækker",{{"HourUTC", type datetime}, {"HourDK", type datetime}})
in
    #"Ændret type1"
Avatar billede micaud Mester
14. april 2022 - 23:50 #5
Hej.

Hele koden virker ikke, men hvis jeg prøver din kilde, så får jeg denne fejl:

DataSource.Error: Web.Contents kunne ikke hente indhold fra 'https://api.energidataservice.dk/datastore_search_sql?sql=SELECT%20*%20FROM%20'elspotprices'%20WHERE%20'PriceArea'%20=%20'DK2'%20AND%20'HourUTC'%20%3E%20timestamp'2020-01-01%2000:00'%20ORDER%20BY%20'HourUTC'%20ASC%20limit%20100' (409): Conflict
Detaljer:
    DataSourceKind=Web
    DataSourcePath=https://api.energidataservice.dk/datastore_search_sql
    Url=https://api.energidataservice.dk/datastore_search_sql?sql=SELECT%20*%20FROM%20'elspotprices'%20WHERE%20'PriceArea'%20=%20'DK2'%20AND%20'HourUTC'%20%3E%20timestamp'2020-01-01%2000:00'%20ORDER%20BY%20'HourUTC'%20ASC%20limit%20100

Så jeg tror ikke man kan bruge dette sprog, når man henter via JSON, men det ville være fedt, hvis SQL koden kunne bruges, der fik jeg adgang, men kun til nogle tabeller.
Avatar billede micaud Mester
15. april 2022 - 11:01 #6
Hej igen.

Jeg er nu kommet noget videre:

Koden er nu:

Min udfordring er, at SQLen tilsyneladende indeholder links til andre tabeller, så jeg kan kun filtrere på "_id" til start.

let
    Kilde = Json.Document(Web.Contents("https://api.energidataservice.dk/datastore_search_sql?" & "sql=SELECT * FROM elspotprices WHERE _id > 2030000")),
    #"Konverteret til tabel" = Table.FromRecords({Kilde}),
    #"Udvidet result" = Table.ExpandRecordColumn(#"Konverteret til tabel", "result", {"records"}, {"result.records"}),
    #"Udvidet result.records" = Table.ExpandListColumn(#"Udvidet result", "result.records"),
    #"Udvidet result.records1" = Table.ExpandRecordColumn(#"Udvidet result.records", "result.records", {"HourUTC", "HourDK", "_id", "PriceArea", "SpotPriceDKK"}, {"result.records.HourUTC", "result.records.HourDK", "result.records._id", "result.records.PriceArea", "result.records.SpotPriceDKK"})
in
    #"Udvidet result.records1"

Når jeg så udvider flere tabeller, så fremkommer "PriceArea" som en kolonne, så hvordan får jeg et filter ind i mit SQL kald til link tabellerne?

Denne kode herunder virker ikke, for "PriceArea" kan ikke findes før udvidelsen:

Kilde = Json.Document(Web.Contents("https://api.energidataservice.dk/datastore_search_sql?" & "sql=SELECT * FROM elspotprices WHERE _id > 2030000 AND PriceArea =DK2"))

Jeg fandt noget med en subquery i query, men hvordan?
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
Uanset kodesprog, så giver vi dig mulighederne for at udvikle det, du behøver.

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester