Forum Discussion

necmik's avatar
necmik
Icon for Microsoft Employee rankMicrosoft Employee
3 years ago
Solved

Populating rows from Web.Contents response

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),

Need Help
  • Poohkrd's avatar
    Poohkrd
    3 years ago

     

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

     

3 Replies

  • Poohkrd's avatar
    Poohkrd
    Icon for Advocate I rankAdvocate I
    3 years ago

     

        ...
    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's avatar
      necmik
      Icon for Microsoft Employee rankMicrosoft Employee
      3 years ago

      It worked great. Very thanks!