Forum Discussion

sara_cgp's avatar
sara_cgp
New Member
3 years ago
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...
  • lbendlin's avatar
    lbendlin
    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"