M JSON Source

Introduction to M JSON Source

M JSON source is a powerful feature in Power Query M language, utilized to parse and transform JSON data into a structured format. JSON (JavaScript Object Notation) is a lightweight data-interchange format that's easy for humans to read and write, and easy for machines to parse and generate.

Using Json.Document Function

The Json.Document function in M language is used to read and convert JSON text into a native M data structure, such as records, lists, and tables. This function is essential for handling JSON data sources within Power Query.

In the example above, a JSON string representing a person's data is parsed into a record in M. Each key-value pair in the JSON object becomes a field in the record.

Loading JSON from a Web Source

Power Query M can also handle JSON data from web sources. This is particularly useful for fetching data from APIs that return JSON formatted responses.

The code snippet above demonstrates how to load JSON data from an API endpoint. The Web.Contents function fetches data from the specified URL, and Json.Document converts it to a structured format.

Handling Nested JSON Structures

JSON data can often be nested, meaning that objects can contain other objects or arrays. The M language provides tools to navigate and extract data from these structures.

In this example, the JSON contains a nested object under the key user. The M script accesses this nested object and assigns it to UserRecord.

Converting JSON Arrays to Tables

When dealing with JSON arrays, you might want to convert them into tables for better analysis and reporting. M provides simple ways to achieve this conversion.

The code above converts a JSON array of objects into a table with a single column, Name. Each object in the array becomes a row in the table.

Conclusion

Understanding how to utilize M JSON source and the Json.Document function allows you to efficiently transform and analyze JSON data within Power Query. Whether you're dealing with simple JSON objects or complex nested structures, the M language provides the necessary tools to handle them effectively.