Forum Discussion
sara_cgp
3 years agoNew Member
Json API returning two different lists
I have a Rest API that its structure is: [ { "unidade": [ "SEC. DE GOVERNO E GABINETE", { "VR_EMP_TOT": 6643133.87, "VR_LIQ_TOT": 3770277.0700...
- 3 years ago
let Source = Json.Document(Web.Contents("https://ptn.saojoaodelrei.mg.gov.br/Relatorios/Graficos.php?STR_EXR_EXR=2023&CHAR_ID_EMP=1&ID2_MES_INI=01&ID2_MES_FIM=12&STR_TP_GRAF=D&LG_IGN_001=S")), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"unidade"}, {"unidade"}), #"Added Custom" = Table.AddColumn(#"Expanded Column1", "Name", each [unidade]{0}), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Records", each [unidade]{1}), #"Expanded Records" = Table.ExpandRecordColumn(#"Added Custom1", "Records", {"VR_EMP_TOT", "VR_LIQ_TOT", "VR_PG_TOT", "LG_PAG"}), #"Removed Other Columns" = Table.SelectColumns(#"Expanded Records",{"Name", "VR_EMP_TOT", "VR_LIQ_TOT", "VR_PG_TOT", "LG_PAG"}) in #"Removed Other Columns"
lbendlin
3 years agoSuper User
let
Source = Json.Document(Web.Contents("https://ptn.saojoaodelrei.mg.gov.br/Relatorios/Graficos.php?STR_EXR_EXR=2023&CHAR_ID_EMP=1&ID2_MES_INI=01&ID2_MES_FIM=12&STR_TP_GRAF=D&LG_IGN_001=S")),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"unidade"}, {"unidade"}),
#"Added Custom" = Table.AddColumn(#"Expanded Column1", "Name", each [unidade]{0}),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Records", each [unidade]{1}),
#"Expanded Records" = Table.ExpandRecordColumn(#"Added Custom1", "Records", {"VR_EMP_TOT", "VR_LIQ_TOT", "VR_PG_TOT", "LG_PAG"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded Records",{"Name", "VR_EMP_TOT", "VR_LIQ_TOT", "VR_PG_TOT", "LG_PAG"})
in
#"Removed Other Columns"sara_cgp
3 years agoNew Member
Thank you so much!! You're a lifesaver.