30 Nov 2024 08:33 AM - last edited on 02 Dec 2024 07:38 AM by MaciejNeumann
I have extracted daily Cpu average , Memory average for servers as json. and importing it into excel create cross join results
for example cpu avg
19th is 12 and 20th 15, but imported result shows
19 - 12
20 - 15
19 - 15
20 - 12
but Csv output shows correct value
let
Fromtime=Excel.CurrentWorkbook(){[Name="fromtime"]}[Content]{0}[Column1],
Source = Json.Document(Web.Contents("https://XXXX/api/v2/metrics/query?metricSelector=
(builtin:host.mem.usage:splitBy(dt.entity.host):max):names&"&Fromtime& "Z&resolution=1d", [Headers=[Authorization="Api-Token XXXXXXXXX"]])),
#"Converted to Table" = Record.ToTable(Source),
#"Transposed Table" = Table.Transpose(#"Converted to Table"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"totalCount", Int64.Type}, {"nextPageKey", type any}, {"resolution", type text}, {"result", type any}}),
#"Expanded result" = Table.ExpandListColumn(#"Changed Type", "result"),
#"Expanded result1" = Table.ExpandRecordColumn(#"Expanded result", "result", {"metricId", "data"}, {"result.metricId", "result.data"}),
#"Expanded result.data" = Table.ExpandListColumn(#"Expanded result1", "result.data"),
#"Expanded result.data1" = Table.ExpandRecordColumn(#"Expanded result.data", "result.data", {"dimensionMap", "timestamps", "values"}, {"result.data.dimensionMap", "result.data.timestamps", "result.data.values"}),
#"Expanded result.data.timestamps" = Table.ExpandListColumn(#"Expanded result.data1", "result.data.timestamps"),
#"Expanded result.data.dimensionMap" = Table.ExpandRecordColumn(#"Expanded result.data.timestamps", "result.data.dimensionMap", {"dt.entity.host.name", "dt.entity.host"}, {"result.data.dimensionMap.dt.entity.host.name", "result.data.dimensionMap.dt.entity.host"}),
#"Expanded result.data.values" = Table.ExpandListColumn(#"Expanded result.data.dimensionMap", "result.data.values"),
#"Removed Columns" = Table.RemoveColumns(#"Expanded result.data.values",{"totalCount", "nextPageKey", "resolution"})
in
#"Removed Columns"
anything missing here