Need help writing M query.

[hxkresl]

I need to import data which i can only connect to with a provided URL.

Questions: '

1.  Do I need M Query?

2.  Can someone show me script for getting data using url, looping through records returned by, and outputting tuples of data to a single table? 

In browser the URL produces JSON format (I am told this is JSON)

{
  /api/data/v8.1/$metadata#mc_agentsites(mc_name)","value":[
    {
      "mc_name":"vianet - Berlin","ar_x002e_mc_name":"ASG - Challant"
    },{
      "mc_name":"Aloa Tama","ar_x002e_mc_name":"AS - MAST - Advertiser Services"
    },{
      "mc_name":"Aloa Tama","ar_x002e_mc_name":"AS - MAST - Advertiser Support"
    }
  ]
}

I pull into pbi using Get Data -> Web and it looks like this:

Converted to Table

List expanded show many records:

Single record expanded:

I can expand only one record at a time.  If I expand a second it over writes the previously expanded record.

[miguel · Accepted solution]

do not expand the records. Instead, when you're here:

look at the menu bar on top and you'll see the List Contextual Menu. In it, you'll see an option (icon) that reads "Convert to Table". Click on that

[miguel]

When you get to the "Expanded List" step, look above on the UI and you'll see an option that says something like "Conver to Table". That will conver the list into a table. Once its in a table, your column that holds all of the records will have a new icon right next to it to expand all of the records into a single table

[hxkresl]

Hi Miguel, i was editing my post when you replied.

When I convert to table I do not get those arrows, instead only list

[miguel]

click on the list and then once you're inside the list you need to conver that into a table

[hxkresl]

Miguel,  when i click on List it expands to records, with no visible option to convert to table.  Records expand to one tuple of data.

[miguel · Accepted solution]

do not expand the records. Instead, when you're here:

look at the menu bar on top and you'll see the List Contextual Menu. In it, you'll see an option (icon) that reads "Convert to Table". Click on that

[hxkresl]

Oh OK.  What delimeter to use for JSON which is delimeted with squiggly parenthesis?

[hxkresl]

I used 'none' for delimeter.  It worked! Thank you Miguel.

[miguel · Accepted solution]

now click on the icon right next to the column name. its an icon that has like 2 arrows that go in opposite directions. That will expand all of the records in that column

[hxkresl]

Yes, thanks for walking me through Miguel. Glad to have the data in PBI finally.