Forum Discussion
Solved
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...
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"
Hey Ibendlin,
Thank you for your help. It worked but I have two questions
Your resolution isn't using the API right? Because it returned only a 2 lines table.
How could I transfer your resolution to work using this API (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)
I have tried a few things that I have seen online, but nothing worked. Sorry.
I'm studying M language Sintaxe but it would be fantastic if you could enlighten me. It's essential for the work I'm doing.
Thank you.
lbendlin
Super User
Super Userlet
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"Thank you so much!! You're a lifesaver.