Microsoft BI Professionals Denmark

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

OAuth2 til Eloverblik /datahub

  • 1.  OAuth2 til Eloverblik /datahub

    Posted May 27, 2020 10:00 AM
    Hej

    Jeg er rimelig ny i Power bi API connections. Efter jeg med success fik implementeret DMI's nye API, fik jeg blod på tanden til at også at trække eldata fra eloverblik.dk. Det er dog noget mere kompliceret, synes jeg, da det kræver et refesh token og et data token.

    Derfor håber jeg at skyde genvej, ved at nogle af jer allerede har lavet en connector til Power bi som I er villige til at dele.

    Jeg håber nogle af jer kan hjælpe mig.

    Her kan I læse lidt mere om, hvad det er for et API:

    Nyt Eloverblik
    Energinet remove preview
    Nyt Eloverblik
    Vi arbejder fortsat på at få rettet mindre fejl og mangler, man skal derfor forvente løbende ændringer på eloverblik.dk Hvis der er spørgsmål, gode forslag eller andet kan i sende en email til datahub@energinet.dk
    View this on Energinet >
    https://energinet.dk/El/Elmarkedet/MDA---Ny-loesning


    ------------------------------
    Esben Søndergaard
    Technical manager
    ------------------------------


  • 2.  RE: OAuth2 til Eloverblik /datahub

    Silver Contributor
    Posted May 29, 2020 07:20 AM
    Hej Esben

    Det er desværre ikke lige så simpelt og ikke en funktionalitet, som du får "ud af boksen" med Power BI. Men med "lidt" M kode, så kan du godt trække en refresh/access token, som så kan anvendes efterfølgende. Her er et eksempel med Microsoft Graph API:

    let
    token_url = "https://login.microsoftonline.com/dccf6c20-4d49-4fea-bd7c-6fb5ba4a2670/oauth2/v2.0/token",
    base_url = "https://graph.microsoft.com/v1.0/groups",

    qry_str = "filter=startswith(displayName,'AAD-Vendor_')",
    body = "grant_type=client_credentials&scope=https://graph.microsoft.com/.default&client_id=[XXX]&client_secret=[YYY]",

    access_token_response = Json.Document(Web.Contents(
    token_url,
    [
    Headers = [#"Content-Type"="application/x-www-form-urlencoded"],
    Content = Text.ToBinary(body)
    ]
    )),
    token = access_token_response[access_token],
    api_url = Text.Combine({base_url,"?$",qry_str}),
    api_headers = [#"Content-Type" = "application/json", Authorization = "Bearer " & token],

    response = Json.Document(Web.Contents(
    base_url,
    [
    RelativePath = "?$filter=startswith(displayName,'AAD-Vendor_')",
    Headers = api_headers
    ]
    )),
    value = response[value],
    #"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    Det skal siges, at jeg på ingen måde har skrevet denne kode helt fra bunden. Jeg har ladet mig inspirere - bl.a. fra blog post fra Chris Webb.

    Venligst
    Just

    ------------------------------
    Just Thorning Blindbæk
    Aarhus N
    ------------------------------



  • 3.  RE: OAuth2 til Eloverblik /datahub

    Posted May 29, 2020 08:24 AM
    Hej Just

    Det var det jeg frygtede:)
    Jeg er ikke sikker på jeg har niveauet til at omskrive din fine kode til at virke med eloverblik API.
    Jeg tror, jeg forsætter med at trække det manuelt for nu. Så må vi se, om jeg får nogle aftener, hvor jeg kan få sat mig ordenligt ind i det.
    Du får tak for svaret.

    ------------------------------
    Esben Søndergaard
    Technical manager
    Sunds
    ------------------------------



  • 4.  RE: OAuth2 til Eloverblik /datahub

    Posted Jun 01, 2020 04:09 PM
    Hej Just

    Nu pirre det lidt til min nysgerrighed, at jeg er gået fra at være ubevist indkompetent til at være bevist inkompetent:)

    Jeg er faktisk kommet et stykke hen af vejen, men er gået lidt fast, hvor jeg skal have sat lidt information ind om min måler:
    Koden der skal ind ser sådan her ud:
    {
    "meteringPoints": {
    "meteringPoint": [
    "5713131353003XXXX"
    ]
    }
    }

    Jeg kan ikke lige finde ud af, hvordan den skal sættet ind i din kode.
    Herunder kan det ses i Curl:

    curl -X POST "https://api.eloverblik.dk/CustomerApi/api/MeterData/GetMeterReadings/2020-01-01/2020-05-01" -H "accept: application/json" -H "Authorization: Bearer "Token" -H "Content-Type: application/json-patch+json" -d "{ \"meteringPoints\": { \"meteringPoint\": [ \"String\" ] }}"

    Jeg håber du kan lede mig på rette vej.

    Mvh Esben

    ------------------------------
    Esben Søndergaard
    Technical manager
    Sunds
    ------------------------------



  • 5.  RE: OAuth2 til Eloverblik /datahub

    Posted Jun 06, 2020 08:12 AM
    Så lykkes det mig at lave en løsning. Hvis andre kan få glæde af  det. Kan I se den her:

    let

    Token_url = "https://api.eloverblik.dk" & "/CustomerApi" & "/api" & "/Token",
    Refresh_token = "Your_API_Refresh_Token",
    Metering_Point = "Your_metering_point",
    Refresh_token_Headers = [Authorization = "Bearer " & Refresh_token],
    access_token_response = Json.Document(Web.Contents(Token_url, [Headers= Refresh_token_Headers])),
    Data_Token = access_token_response[result],
    base_url = "https://api.eloverblik.dk" & "/CustomerApi" & "/api" & "/MeterData/GetMeterReadings",
    From_date = "2015-01-01",
    To_date = Date.ToText(DateTime.Date(DateTime.LocalNow()),"yyyy-MM-dd"),
    Aggregation = "Day",
    api_url = Text.Combine({base_url,"/",From_date,"/",To_date}),
    api_headers = [Authorization = "Bearer " & Data_Token, #"Content-Type"="application/json"],
    body = "{
    ""meteringPoints"": {
    ""meteringPoint"": [
    "& Metering_Point &"
    ]
    }
    }",

    Source = Json.Document(Web.Contents(api_url, [Headers= api_headers, Content=Text.ToBinary(body)])),
    #"Converted to Table" = Record.ToTable(Source),
    #"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),
    #"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"result", "success", "errorCode", "errorText", "id", "stackTrace"}, {"Value.result", "Value.success", "Value.errorCode", "Value.errorText", "Value.id", "Value.stackTrace"}),
    #"Expanded Value.result" = Table.ExpandRecordColumn(#"Expanded Value1", "Value.result", {"meteringPointId", "readings"}, {"Value.result.meteringPointId", "Value.result.readings"}),
    #"Expanded Value.result.readings" = Table.ExpandListColumn(#"Expanded Value.result", "Value.result.readings"),
    #"Expanded Value.result.readings1" = Table.ExpandRecordColumn(#"Expanded Value.result.readings", "Value.result.readings", {"readingDate", "registrationDate", "meterNumber", "meterReading", "measurementUnit"}, {"Value.result.readings.readingDate", "Value.result.readings.registrationDate", "Value.result.readings.meterNumber", "Value.result.readings.meterReading", "Value.result.readings.measurementUnit"})
    in
    #"Expanded Value.result.readings1"

    ------------------------------
    Esben Søndergaard
    Technical manager
    Sunds
    ------------------------------



  • 6.  RE: OAuth2 til Eloverblik /datahub

    Posted Oct 01, 2020 06:29 AM
    Edited by Guillaume Albert Oct 01, 2020 07:03 AM


  • 7.  RE: OAuth2 til Eloverblik /datahub

    Posted Oct 01, 2020 02:36 PM
    I get the same Bad request error today. I used the code yesterday and it worked fine. I have just used an hour to trobleshot without luck.
    It seams like the swagger is still working. I will not use more time on it today. Maybe I will have the time in this weekend.

    https://api.eloverblik.dk/CustomerApi/swagger/index.html

    ------------------------------
    Esben Søndergaard
    Technical manager
    Sunds
    ------------------------------



  • 8.  RE: OAuth2 til Eloverblik /datahub

    Posted Oct 02, 2020 01:37 AM
    HI, I actually managed to debug it. There was " missing and some other discrepancies which I found by looking through the API documentation. so it was mostly query formatting.
    Here it is for readings;

    let

    Token_url = "https://api.eloverblik.dk" & "/CustomerApi" & "/api" & "/Token",
    Refresh_token = "YOURREFRESHTOKENHERE",
    Metering_Point = "YOURIDPOINTHERE",
    Refresh_token_Headers = [Authorization = "Bearer " & Refresh_token],
    access_token_response = Json.Document(Web.Contents(Token_url, [Headers= Refresh_token_Headers])),
    Data_Token = access_token_response[result],
    base_url = "https://api.eloverblik.dk" & "/CustomerApi" & "/api" & "/MeterData/GetMeterReadings",
    From_date = "2016-01-01",
    To_date = Date.ToText(DateTime.Date(DateTime.LocalNow()),"yyyy-MM-dd"),
    Aggregation = "Hour",
    api_url = Text.Combine({base_url,"/",From_date,"/",To_date}),
    api_headers = [Authorization = "Bearer " & Data_Token, #"Content-Type"="application/json"],

    body = "{""meteringPoints"": {""meteringPoint"": ["""& Metering_Point &"""]}}",
    Source = Json.Document(Web.Contents(api_url, [Headers= api_headers, Content=Text.ToBinary(body)]))
    in
    Source

    And here it is for TimeSeries:
    let

    Token_url = "https://api.eloverblik.dk" & "/CustomerApi" & "/api" & "/Token",
    Refresh_token = "YOURREFRESHTOKENHERE",
    Metering_Point = "YOURIDPOINTHERE",
    Refresh_token_Headers = [Authorization = "Bearer " & Refresh_token],
    access_token_response = Json.Document(Web.Contents(Token_url, [Headers= Refresh_token_Headers])),
    Data_Token = access_token_response[result],
    base_url = "https://api.eloverblik.dk" & "/CustomerApi" & "/api" & "/MeterData/GetMeterReadings",
    From_date = "2016-01-01",
    To_date = Date.ToText(DateTime.Date(DateTime.LocalNow()),"yyyy-MM-dd"),
    Aggregation = "Hour",
    api_url = Text.Combine({base_url,"/",From_date,"/",To_date}),
    api_headers = [Authorization = "Bearer " & Data_Token, #"Content-Type"="application/json"],

    body = "{""meteringPoints"": {""meteringPoint"": ["""& Metering_Point &"""]}}",
    Source = Json.Document(Web.Contents(api_url, [Headers= api_headers, Content=Text.ToBinary(body)]))
    in
    Source

    ------------------------------
    Guillaume Albert
    ------------------------------



  • 9.  RE: OAuth2 til Eloverblik /datahub

    Posted Oct 02, 2020 02:41 AM
    Edited by Esben Søndergaard Oct 02, 2020 02:56 AM
    Great
    After updating the body I dont get the bad request anymore. It works with one metering point
    Metering_Point = "57131313960000xxxx",
    body = "{""meteringPoints"": {""meteringPoint"": ["""& Metering_Point &"""]}}"

    When I add more meteringpoints like this:
    Metering_Point = """57131313960000xxxx"", ""57131313960002xxxx"", ""57131313960002xxxx"", ""57131313960002xxxx""",
    body = "{""meteringPoints"": {""meteringPoint"": ["& Metering_Point &"]}}"

    It is also working. It must be more the second solution that is more generic.

    I cannot finde the other changes you have made. I am newbe in coding, therefore I will like to correct what you have found
    ------------------------------
    Esben Søndergaard
    Technical manager
    Sunds
    ------------------------------



  • 10.  RE: OAuth2 til Eloverblik /datahub

    Posted Oct 02, 2020 03:45 AM
    Great
    After updating the body I dont get the bad request anymore. It works with one metering point
    Metering_Point = "57131313960000xxxx",
    body = "{""meteringPoints"": {""meteringPoint"": ["""& Metering_Point &"""]}}"

    When I add more meteringpoints like this:
    Metering_Point = """57131313960000xxxx"", ""57131313960002xxxx"", ""57131313960002xxxx"", ""57131313960002xxxx""",
    body = "{""meteringPoints"": {""meteringPoint"": ["& Metering_Point &"]}}"

    It is also working. It must be more the second solution that is more generic.

    I cannot finde the other changes you have made. I am newbe in coding, therefore I will like to correct what you have found
    ------------------------------
    Esben Søndergaard
    Technical manager
    Sunds

    ------------------------------
    Esben Søndergaard
    Technical manager
    Sunds
    ------------------------------



  • 11.  RE: OAuth2 til Eloverblik /datahub

    Posted Oct 02, 2020 03:49 AM
    Do you have problems with the connection to the source? It is a problem that I see often in Excel version of my query.


    ------------------------------
    Esben Søndergaard
    Technical manager
    Sunds
    ------------------------------



  • 12.  RE: OAuth2 til Eloverblik /datahub

    Posted Nov 01, 2020 09:29 AM
    Edited by Jacob Fischer Nov 01, 2020 09:46 AM
    When I use the Query-string to pull data from my meter through Eloverblik I only get 10 rows of data using from date 2016-01-01 and till today.

    My electricity meter did however first get hourly settled by 2020-07-01, but from this date I can see my hourly consumption on Eloverblik, so it seems wierd to me why I don't get hourly consumption from the string?

    Here's the value-readings-date tab from within Query Editor:

    Value.result.readings.readingDate
    08/14/2016 22:00:00
    09/14/2016 22:00:00
    12/18/2016 23:00:00
    12/31/2017 23:00:00
    10/03/2018 22:00:00
    10/03/2018 22:00:00
    12/31/2018 23:00:00
    12/31/2019 23:00:00
    06/30/2020 22:00:00
    09/30/2020 22:00:00

    I tried editing line 12 (Aggregation) in the Query Editor from Hour to Day, Minute, Year, but that doesn't seem to change anything, I'm still only getting the same 10 lines of data.

    Does anyone know why I'm not getting hourly consumption after 2020-07-01 ?

    ------------------------------
    Jacob
    ------------------------------