Populating rows from Web.Contents response

[necmik]

Hi all,

I am new to M language.

I want to create a table from scratch and its rows to be populated from a GET web service called by Web.Contents.

Suppose the table schema is: #table({"Country"}, {"Province"}, {"City"}) and response of the web service is:

[
{
"Cntry": "Serbia",
"Prvc": "Belgrade",
"City": "Belgrade",
"Population": 200000
},
{
"Country": "Turkey",
"Province": "Istanbul",
"City": "Istanbul",
"Population": 18000000
}
]

After getting the response json, how can we insert the records to the table? 

ResponseContent = Web.Contents(<URL>),
ResponseJson = Json.Document(ResponseContent),

[Poohkrd · Accepted solution]

    ...
    Custom0 = List.Transform( ResponseJson, (t)=>Table.FromRecords({t}) ),
    Custom1 = Table.Combine( Custom0 ),
    MergedColumns = Table.CombineColumns(Custom1,{"Country", "Cntry"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Country"),
    MergedColumns1 = Table.CombineColumns(MergedColumns,{"Province", "Prvc"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Province")
in
    MergedColumns1

or something like this:

    ...
    Custom0 = List.Transform( ResponseJson, Record.ToList ),
    Custom1 = Table.FromRows( Custom0, Record.FieldNames( ResponseJson{1} ) )
in
    Custom1

[necmik]

It worked great. Very thanks!

[Anonymous]

Hi @necmik ,

Maybe this similar post helps you. Please refer to

Solved: How to add body into Web.Contents()? - Microsoft Power BI Community

The solution in this post is from

Specifying JSON Query in Power Query

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.