cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

metric extracted json import do cross join results

Vijayt
Participant

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"

 

 

Vijayt_0-1732955426976.png

anything missing here 

 

 

0 REPLIES 0

Featured Posts