Posts Tagged ‘JSON’

Automatically expand all fields from a JSON document in Power BI and Power Query

If you work with JSON documents that are new to you, it can be very helpful to fully expand JSON to see at a glance what’s in there. The following function has you covered for this task. It returns a table with all values in column “Value” and additional columns describing where that value came from in a hierarchical form, no matter how deep the nesting goes:

Example

See below what this function does for the following JSON :

Automatically expand JSON: Sample JSON

{"A":"A Record", "B":["ListItem1", {"C":"A nested Record", "D":"Another nested Record"}], "E":{"F":["NestedListItem1","NestedListItem2","NestedListItem3"]}}

Automatically expand JSON: Result

 

  • Value: The respective values
  • Level: Main levels.
  • Sort: Sort column with hierarch to display in report
  • SortBy: Sort your “Sort”-column in the data model by this column: It will be filled up with “0” according to the maximum value within the respective position. This effectively allows sorting by number, although it is a text-field.
  • Name-columns: They display the hierarchical location of the value with regards to JSONs record field names.

The Function

 

How the code works

  • The main elements in JSON are records and lists and they can appear in many different combinations. So to handle them equally, I often convert them into a common type so that they can be further processed equally (row 6, 27 and 32).
  • I use List.Generate (row 10-42) to repetitively check whether the returned values are further expandable or not. If they are not expandable, they go into “Finished” (row 25) and if they need further expanding, they land in “Unfinished” (row 26) and will be expanded further.
  • Row 48-56 contain a dynamic padding that will convert the Sort-entry “2.1” into “02.1” in column “SortBy”, if there is also a “10.x” in the column. This will make sure that the (alphabetical) sort returns “2.1” before “10.1”. (And if there is a 100.x it will convert to “002.1” and so on…)

I’m pretty sure that this code can be further improved, as it has been evolved for quite some time and I didn’t re-engineer it. So if you’re up for it, please don’t hesitate to post an improved version!

For large JSONs, the table can get very long and it could be beneficial to view it in a more compact form (actually a form that would provide tables for a relational model). I will show this in an upcoming article, so if you’re interested in it, make sure to subscribe to my blog not to miss it.

Enjoy & stay queryious 😉

Easy POST requests with Power BI and Power Query using Json.FromValue

The function Json.FromValue provides a super-easy way to create POST-calls to web services that require JSON format in their body parameters.

Background

If you want to make a POST request to a web service through Power Query, you have to add the relevant data in the “Content”-parameter of the query (see Chris Webb’s article here for example). This is a pretty nifty method that transforms the default GET-method to a POST automatically. The content of that parameter has to be passed in as a binary and therefore the Text.ToBinary function can be used. This will serve well in many cases, but if your service requires a JSON record and you happen to have that record somewhere in your query already, transforming it to text can get pretty cumbersome and is actually not necessary:

Problem

Say you want to use Microsoft’s Translate API to translate values from a column to a different language. This API lets you pass in multiple strings into one call if you pass them in as a JSON array of records. So instead of transforming them all into a long string of text that represents the JSON-syntax, you can simply let come

Json.FromValue to the rescue

List.Transform ( YourColumn, each [Text=_] )

will transform “YourColumn” into a list of records that represents the required JSON-array.

The function Json.FromValue (which hides itself in the Text-category of M-functions) takes actually in ANY format from Power Query and transforms it into a binary JSON-format. Pass this into the Content-parameter and you’re good to go.

Note: There is a little flaw with the current version of the MS Translate API and in my next blogpost I will show how to tackle it.

Enjoy & stay queryious 😉

How to open a complex JSON record in Power BI and Power Query

Today I’ll show you a very useful technique how to deal with a JSON record that contains a wild mixture of different elements like this:

If you click on one of the expandable elements, their content will be shown, but you’ll loose all the “surrounding” information (metadata) that is visible now. This is often an issue, regardless if you want to create multiple tables from it to build a star-schema or just need a handful of fields or a denormalized table. But with a little help from M, you’re good to go:

Table.FromRecords( { MyJsonRecord } )

Will returns this:

With this move, every expansion of one of the expandable elements will keep the existing data in place:

Create one big flat table

Simply expand one element after each other to create a denormalized table

Create star schema

For multiple tables, keep this query and reference it to create you (sub-)tables. Always keep the Id-column as the key (!) to combine all the tables in your data model later. (Provided you use this in a function for multiple entities/series)

Best is to play with it, so just past this code into the advanced editor:

 

If your JSON-record has a different structure with “just” header and data in different fields, this technique will be more suitable for you: http://www.thebiccountant.com/2016/04/23/universal-json-opener-for-quandl/

Enjoy & stay queryious 🙂

How to expand a column that cannot be expanded in Power BI and Power Query in Excel

Especially when working with JSON-data, you might end up with a column that has elements of mixed types in it. The expand column – arrows will be missing, but some elements still need to be expanded, like here:

But there is an easy way to fix it:

Transform to expandable column

Table.TransformColumns(Source, {{“Column1”, each if Value.Is(_, type list) then _ else {_} }} )

It transforms the “Column1” from table “Source” by checking, if the content of the each row ( _ ) is of type list and if yes, keep that value ( _ ) and if not, transform it to a list (by framing it into curly brackets {_} )

Syntax for tables

Table.TransformColumns(Source, {{“Column1”, each if Value.Is(_, type table) then _ else #table({“Column1”}, {{_}} ) }} )

Syntax for records

Table.TransformColumns(Source, {{“Column1”, each if Value.Is(_, type record) then _ else [a=_] }} )

File for Subscribers to download: HTExpandColumnThatCannotBeExpanded.zip

This technique should be applied to columns where the expandable elements all have the same structure. If that’s not the case, you should use this technique instead.

Enjoy & stay queryious 😉