Cannot open response in Excel

Just getting started
#1

I tried to open this response in excel but did not succeed:
This is the response: [“id\tcountry\tcity”,“1\tNLD\tGroningen”,“2\tNLD\tAssen”,“3\tNLD \tLeeuwarden”,""]
What I did in excel:
Get data from file->json
Then I got a number of lines in power query editor :header = List and content per line Record.
The I choose convert to table : For delimiter I tried several but when choosing comma I get : error cannot convert a value of type Record to type text.

What am I doing wrong ?

#2

Hi @johankr

Not 100% sure I understand what you’re trying to do here, but when I save that response as JSON and import it to excel using “From Text” (with comma delimiter) it works…

image

#3

That worked indeed, don’t understand that it doesn’t work when I choose “from JSON”.

#4

I would guess it’s because it’s not in JSON format;

I use tools like this to help me validate my JSON;

image

Hope it helps… :+1: