Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
sara_cgp
New 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.070000004,
          "VR_PG_TOT": 3131085.419999999,
          "LG_PAG": false
        }
      ]
    },
    {
      "unidade": [
        "FUNDO MUNIC. DE PROTEÇÃO E DEFESA DO CONSUMID",
        {
          "VR_EMP_TOT": 41096.01,
          "VR_LIQ_TOT": 41096.01,
          "VR_PG_TOT": 34889.68,
          "LG_PAG": false
        }
      ]
    },
]  
 
When I connect it on Power Query, it return this:
 
pwrbi1.png
Then I transform it on a table:
pwrbi2.png
When I click on Expand, it brings me another list:
pwrbi3.png
And from there:
pwrbi5.png
Inside the "Record" I have 4 lines that I need to be columns:
pwrbi6.pngBasically I need a table like this:
 
column1.unidade       |    VR_EMP_TOT     |    VR_LIQ_TOT    | VR_PG_TOT     |     LG_PAG
Sec. de Governo        |    66545                 |        541654       |   5465              |   False
Fundo Munc.             |   54654                  |        548994       |   5474              |   False
Sec Munc                  |  52458                  |        55444          |   4484              |   False
....
 
Someone could bring me some light please! It's possible to do that?
1 ACCEPTED SOLUTION

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"

View solution in original post

4 REPLIES 4
lbendlin
Super User
Super User

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZC9asMwFEZf5aLZCKlSLbmbGyvGEEuuf7LIwgSSQiFkKZ1Cpgx5sLxYrYQam4aUQO/0Xb5z73CsRRa5wCLoZz8kgBZ97T7Wq/WmRS9gR4WvKjXDkChIzVKV2kCf4tdMq1q1KJiw+8nmb5dlp/Kiq03tP4chZ5QxLEVwi1xkbz8kE4I8CYGJIJfhNw+KdOApo0Q+Y06j6/zmF2lXxKmH31fbz82kP4w2N+RD8LiseaMTA3mjs6u0ojS1Op/OR+8tUXNVxdADM6OrJs+SRw1ySqIQE/qXv7vcSBuXMsKh/Bdbl+QAkHPf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    res = Json.Document(Text.Combine(Source[Column1])),
    #"Converted to Table" = Table.FromList(res, 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"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

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_I...

 

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.

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"

Thank you so much!! You're a lifesaver. 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors