Expression.Error: We cannot convert a value of type Record to type Text.

[jannahds]

I know others have had this problem, however I've been unable to follow directions elsewhere, so I'm going to selfishly ask for more help here...

I'm importing web data into Power BI via an API.  It comes in JSON structure.

What I've done:

1) Click on Get Data

2) Web data

3) Put in my url and authorization token

4) So it brings up my JSON data as a LIST of RECORDS

But when I click on convert "to Table", and select semicolon as delimiter, it gives this error:

Expression.Error: We cannot convert a value of type Record to type Text.
Details:
Value=[Record]
Type=[Type]

So, within the record, there are a number of different columns.  Many of the columns hold integer or string data.  But a few of the columns hold other "LIST" or "RECORD" types.

I am not used to dealing with JSON data and I'm newish to Power BI, so if you're able to help me figure out how to do this in the Power Query, I'd be really greatful.  Thanks!

Janna

[parry2k]

@jannahds can help but it will be easier if you can share pbix file with data and can look into the transformation otherwise it is a bit hard to provide a solution.

[jannahds]

Thanks for your help @parry2k 

I've put the pbix file here, if you can make sense of it:  https://drive.google.com/file/d/1q9LjPH7mHi0Ll7SK4xNNKwNxjENQAuTF/view?usp=sharing

Thanks!

[parry2k]

@jannahds unfortunately I cannot do anything because PQ requires credentials to look at the JSON data. 

[jannahds]

Hi, can you let me know what that means? or what credentials are needed? 

Thanks!

[v-yiruan-msft · Accepted solution]

Hi @jannahds ,

Please try to do the below steps to get the table:

1. Right select List and select To Table (leave the delimiter as None)

Convert to Table

2. Select the column split icon in the Column1 header. Select OK

Expand

I just updated your report file with the above steps,  you can get it from this link. In addition, you can review this documentation to get more details about how to expand a list of records in a query.

Best Regards

Rena

[kleighton]

Thank You!! I have spent 1.5 days trying to figure this out, i also was trying to use comma as a delimiter at the "To Table stage". 

[jannahds]

Perfect - thank you so much.  I had been using "semicolon" as the delimiter, as I've been used to using a .csv file of the data which uses semicolons, and I think that's where I was getting confused.

I now have it working.