Json.FromValue

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Json.FromValue

Understanding JSON

Before we dive into the M code behind Json.FromValue, let’s first understand what JSON is and why it’s important. JSON, or JavaScript Object Notation, is a lightweight text-based format for representing data. It’s easy for humans to read and write, and it’s also easy for machines to parse and generate. JSON is commonly used for web-based data exchange and storage, and it’s also used for configuration files and other data that needs to be easily readable and writable.

JSON is essentially a collection of key-value pairs, where the keys are strings and the values can be any valid JSON data type, including strings, numbers, booleans, nulls, arrays, and objects. Here’s an example of a simple JSON document:

{

“name”: “John Doe”,

“age”: 30,

“isMarried”: false,

“hobbies”: [“reading”, “cooking”, “traveling”],

“address”: {

“street”: “123 Main St”,

“city”: “New York”,

“state”: “NY”,

“zip”: “10001”

}

}

As you can see, the document contains five key-value pairs, where the values include a string, a number, a boolean, an array, and an object.

The Json.FromValue Function

The Json.FromValue function in Power Query M converts a value into a JSON document. The value can be any valid Power Query M expression, including a record, a list, a table, or a primitive value such as a number or a string.

Here's the syntax for the Json.FromValue function:

Json.FromValue(value as any, optional options as nullable record) as text

The function takes two arguments: the value to be converted and an optional record of options. The value can be any valid Power Query M expression, and the optional options record can be used to specify options such as the indentation level and whether to include null values.

Here's an example of using the Json.FromValue function to convert a record into a JSON document:

let

myRecord = [name=”John Doe”, age=30, isMarried=false],

myJson = Json.FromValue(myRecord)

in

myJson

The resulting JSON document will look like this:

{

“name”: “John Doe”,

“age”: 30,

“isMarried”: false

}

As you can see, the Json.FromValue function has converted the record into a JSON document with three key-value pairs.

Understanding the M Code

Now that we understand what JSON is and how the Json.FromValue function works, let's take a closer look at the M code behind the function.

The M code for the Json.FromValue function is relatively simple. Here's what it looks like:

(value as any, optional options as nullable record) =>

let

indent = if Record.HasFields(options, “Indent”) then options[Indent] else 0,

includeNulls = if Record.HasFields(options, “IncludeNulls”) then options[IncludeNulls] else false,

json = Json.Document(Json.FromValue(value, null)),

formattedJson = if indent = 0 then json else Json.Transform(json, {{“”, each Text.PadStart(“”, indent [Level], ” “) & _}})

in

if includeNulls then Text.Replace(formattedJson, “null”, “null”) else Text.Replace(formattedJson, “, null”, “”)

The function takes the value and options arguments, and then performs the following steps:

1. It checks the options record for the "Indent" field, and if it's present, sets the indent variable to the specified value. If the "Indent" field is not present, the indent variable is set to 0.

2. It checks the options record for the "IncludeNulls" field, and if it's present, sets the includeNulls variable to the specified value. If the "IncludeNulls" field is not present, the includeNulls variable is set to false.

3. It uses the Json.FromValue function to convert the value into a JSON document.

4. It formats the JSON document using the Text.PadStart function to add indentation based on the specified indent level.

5. It replaces any null values with either the string "null" or an empty string, depending on the value of the includeNulls variable.

6. It returns the formatted JSON document as text.

In conclusion, the Json.FromValue function in Power Query M is a powerful tool for converting data into the JSON format. The M code behind the function is relatively simple, but it provides a great deal of flexibility and control over the resulting JSON document. By understanding the M code behind the function, you can more effectively use the function in your data transformation and analysis tasks, and you can also customize the function to meet your specific needs.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)