importing data into Excel from a JSON file by a web url with power query, getting [Lis]

Question

I am importing data into Excel from a JSON file by a web url, using From Web under the Data tab power query.

Most of the data is coming in correctly but some of the data is coming is as [List] as the cell value

All [List] data is of the form aaa|bbb|ccc i.e piped delimited

Should look like this

Is it possible to get the data to show properly?

Thanks

Answer 1

Hi Tim,

You should check again the query, those columns that contain List should be expanded, or the values should be extracted, because those can be complex JSON elements: there can be lists of records, or lists of lists.

You can even use a simple step to display as text whatever is in that column:

ConvertAsText= Table.TransformColumns(#"Previous Step Name", {"Your Column Name", each Text.FromBinary(Json.FromValue(_)), type text})

This conversion will be able to display any type of JSON elements.

Answer 2

Hi Tim,

There are some limits, see this page: Power Query Limits

Query name length	80 characters

You have reached this limit, which is reasonable ( in my opinion, of course).

Not much you can do, it's better to keep the names below 80 chars.

Answer 3

Hi Tim,

We need more information regarding your concern. What's the full version number of your Office 365? To check, kindly refer to the steps in this article: About Office: What version of Office am I using?. Also, were you able to successfully import data from a JSON file through a web URL before?

In the meantime, try repairing your Office 365. To do so, kindly follow the steps in this article: Repair or remove programs in Windows 10. Once done, check if the issue persists.

We look forward to your response.

Answer 4

Hello Catalin, thanks for this it worked great!

If I have multiple columns where this is a issue is there a way to run the provided code on multiple columns or do I add the code for each column separably?

Or can is there a similar code for ruining on an entire sheet and thereby fix the issue where ever it exists without specifying the column?

Answer 5

Hi Tim,

Yes, the TransformColumns function, as the name itself says, can transform multiple columns in the same step, no need to add multiple steps for this.

The syntax for multiple columns looks like this:

Table.TransformColumns(<Previous Step Name>,

{

{ <column name>, <operation>, type <type name> },

{ <column name>, <operation>, type <type name> },

{<column name>, <operation>, type <type name> }

}

)

In your case, it will be:

ConvertAsText= Table.TransformColumns(#"Previous Step Name", { { "First Column Name", each Text.FromBinary(Json.FromValue(_)), type text}, {"Second Column Name", each Text.FromBinary(Json.FromValue(_)), type text } } )

Or can is there a similar code for ruining on an entire sheet and thereby the issue where ever it exists?

I guess you wanted to ask if it is possible to write a query that will expand completely a json object. There are a few solutions, like this one: https://gist.github.com/Hugoberry/4ad49f4301edf47fffe2ef06aed61513

But I've never seen a solution that works on any json object, there are some very complex json structures, each object must be analyzed separately. A record needs to be expanded in new columns, a list of records can be expanded in new rows then the records expanded in new columns.

Instead of expanding a list of records to new rows, I prefer to combine the list of records into a single text string, with a custom function:

(ListOfRecords as list)=>

let

//Source=option_values,

Source=ListOfRecords,

    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each Text.Combine(Table.ToList(Table.TransformColumnTypes(Record.ToTable([Column1]),{{"Value", type text}}),Combiner.CombineTextByDelimiter(": ")),", ")),

    Combined= Text.Combine(#"Added Custom"[Custom],"#(lf)")

in

    Combined

The list of records converted to text with this function will look like this, in a single cell:

id: 516553, name: Main Category, value: Pulls & Handles

id: 516554, name: Sub-Category, value: Straight Pulls

id: 516555, name: Sub-Category, value: Ornate Pulls

id: 516556, name: Sub-Category, value: Bar Pulls

id: 516557, name: Collection, value: Liscio

id: 516558, name: Style, value: Modern

Each item from the list is separated with a line feed, each item from a record is separated with a ", " separator, both separators can be changed in the function.