-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathMashup_PQ_Eloverblik.txt
58 lines (58 loc) · 7.33 KB
/
Mashup_PQ_Eloverblik.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
let
//Vi definerer
Token_url = "https://api.eloverblik.dk" & "/CustomerApi" & "/api" & "/Token",
Refresh_token = #"Refresh Token (Lange kode)",
Metering_Point = #"Målepunkts ID",
Refresh_token_Headers = [Authorization = "Bearer " & Refresh_token],
//Vi sender en forespørgsel for at få adgang
access_token_response = Json.Document(Web.Contents(Token_url, [Headers= Refresh_token_Headers])),
//Vi får et resultat
Data_Token = access_token_response[result],
//Vi definerer nærmere hvornår vi vil have data fra og til og hvilket tidsinterval
base_url = "https://api.eloverblik.dk" & "/CustomerApi" & "/api" & "/MeterData/GetTimeSeries",
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,"/Hour"}),
api_headers = [Authorization = "Bearer " & Data_Token, #"Content-Type"="application/json"],
body = "{""meteringPoints"": {""meteringPoint"": ["""& Metering_Point &"""]}}",
//Vi executer og sender en forespørgsel
Source = Json.Document(Web.Contents(api_url, [Headers= api_headers, Content=Text.ToBinary(body)])),
#"Converted to Table" = Record.ToTable(Source),
Value = #"Converted to Table"[Value],
#"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table1", "Column1"),
#"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"MyEnergyData_MarketDocument", "success", "errorCode", "errorText", "id", "stackTrace"}, {"Column1.MyEnergyData_MarketDocument", "Column1.success", "Column1.errorCode", "Column1.errorText", "Column1.id", "Column1.stackTrace"}),
#"Expanded Column1.MyEnergyData_MarketDocument" = Table.ExpandRecordColumn(#"Expanded Column2", "Column1.MyEnergyData_MarketDocument", {"mRID", "createdDateTime", "sender_MarketParticipant.name", "sender_MarketParticipant.mRID", "period.timeInterval", "TimeSeries"}, {"mRID", "createdDateTime", "sender_MarketParticipant.name", "sender_MarketParticipant.mRID", "period.timeInterval", "TimeSeries"}),
#"Expanded TimeSeries" = Table.ExpandListColumn(#"Expanded Column1.MyEnergyData_MarketDocument", "TimeSeries"),
#"Expanded TimeSeries1" = Table.ExpandRecordColumn(#"Expanded TimeSeries", "TimeSeries", {"mRID", "businessType", "curveType", "measurement_Unit.name", "MarketEvaluationPoint", "Period"}, {"mRID.1", "businessType", "curveType", "measurement_Unit.name", "MarketEvaluationPoint", "Period"}),
#"Expanded Period" = Table.ExpandListColumn(#"Expanded TimeSeries1", "Period"),
#"Expanded Period1" = Table.ExpandRecordColumn(#"Expanded Period", "Period", {"resolution", "timeInterval", "Point"}, {"resolution", "timeInterval", "Point"}),
#"Expanded Point" = Table.ExpandListColumn(#"Expanded Period1", "Point"),
#"Expanded Point1" = Table.ExpandRecordColumn(#"Expanded Point", "Point", {"position", "out_Quantity.quantity", "out_Quantity.quality"}, {"position", "out_Quantity.quantity", "out_Quantity.quality"}),
#"Expanded timeInterval" = Table.ExpandRecordColumn(#"Expanded Point1", "timeInterval", {"start", "end"}, {"start", "end"}),
#"Expanded MarketEvaluationPoint" = Table.ExpandRecordColumn(#"Expanded timeInterval", "MarketEvaluationPoint", {"mRID"}, {"mRID.2"}),
#"Expanded mRID.2" = Table.ExpandRecordColumn(#"Expanded MarketEvaluationPoint", "mRID.2", {"codingScheme", "name"}, {"codingScheme", "name"}),
#"Expanded period.timeInterval" = Table.ExpandRecordColumn(#"Expanded mRID.2", "period.timeInterval", {"start", "end"}, {"start.1", "end.1"}),
#"Expanded sender_MarketParticipant.mRID" = Table.ExpandRecordColumn(#"Expanded period.timeInterval", "sender_MarketParticipant.mRID", {"codingScheme", "name"}, {"codingScheme.1", "name.1"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Expanded sender_MarketParticipant.mRID", "start", Splitter.SplitTextByEachDelimiter({"T"}, QuoteStyle.Csv, false), {"start.1.1", "start.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"start.1.1", type date}, {"start.2", type datetime}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"position", Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([position] <> 25)),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each [position]-1),
#"Added Custom Column" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.PadStart(Text.From([Custom], "da-DK"), 2, "0"), type text),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom Column",{"mRID", "createdDateTime", "sender_MarketParticipant.name", "codingScheme.1", "name.1", "start.1", "end.1", "mRID.1", "businessType", "curveType", "measurement_Unit.name", "codingScheme", "name", "resolution", "start.1.1", "start.2", "end", "position", "Custom", "out_Quantity.quantity", "out_Quantity.quality", "Column1.success", "Column1.errorCode", "Column1.errorText", "Column1.id", "Column1.stackTrace"}),
#"Reordered Columns1" = Table.ReorderColumns(#"Reordered Columns",{"mRID", "createdDateTime", "sender_MarketParticipant.name", "codingScheme.1", "name.1", "start.1", "end.1", "mRID.1", "businessType", "curveType", "measurement_Unit.name", "codingScheme", "name", "resolution", "start.1.1", "start.2", "end", "position", "Custom", "Custom.1", "out_Quantity.quantity", "out_Quantity.quality", "Column1.success", "Column1.errorCode", "Column1.errorText", "Column1.id", "Column1.stackTrace"}),
#"Duplicated Column" = Table.DuplicateColumn(#"Reordered Columns1", "Custom.1", "Custom.1 - Copy"),
#"Reordered Columns2" = Table.ReorderColumns(#"Duplicated Column",{"mRID", "createdDateTime", "sender_MarketParticipant.name", "codingScheme.1", "name.1", "start.1", "end.1", "mRID.1", "businessType", "curveType", "measurement_Unit.name", "codingScheme", "name", "resolution", "start.1.1", "start.2", "end", "position", "Custom", "Custom.1", "Custom.1 - Copy", "out_Quantity.quantity", "out_Quantity.quality", "Column1.success", "Column1.errorCode", "Column1.errorText", "Column1.id", "Column1.stackTrace"}),
#"Removed Other Columns" = Table.SelectColumns(#"Reordered Columns2",{"start.1", "end.1", "measurement_Unit.name", "start.1.1", "start.2", "end", "position", "Custom", "Custom.1", "Custom.1 - Copy", "out_Quantity.quantity"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Removed Other Columns", "end", Splitter.SplitTextByDelimiter("T", QuoteStyle.Csv), {"end.1.1", "end.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"end.1.1", type date}, {"end.2", type datetime}}),
#"Removed Other Columns1" = Table.SelectColumns(#"Changed Type2",{"start.1.1", "start.2", "end.1.1", "Custom.1 - Copy", "out_Quantity.quantity"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Removed Other Columns1",{{"Custom.1 - Copy", type time}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type3",{{"out_Quantity.quantity", "kWh"}, {"Custom.1 - Copy", "Tidspunkt"}, {"end.1.1", "Dato"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns",".",",",Replacer.ReplaceText,{"kWh"}),
#"Changed Type4" = Table.TransformColumnTypes(#"Replaced Value",{{"kWh", type number}}),
#"Removed Other Columns2" = Table.SelectColumns(#"Changed Type4",{"Dato", "Tidspunkt", "kWh"})
in
#"Removed Other Columns2"