Introduction: Grasping JSON Data and Power Query
JSON (JavaScript Object Notation) serves as a lightweight format for data interchange, designed for human readability while being machine-friendly for parsing and generation. This format is prevalent in web services and APIs for data exchange between clients and servers. JSON’s architecture typically encompasses nested arrays and objects, making it versatile for various data types, yet also presenting challenges during data transformation and analysis. Power Query acts as a data connection technology, allowing users to discover, connect, combine, and refine data from a diverse array of sources. Integrated with Microsoft applications like Excel and Power BI, it provides a robust toolset for transforming and preparing data for analytical purposes. The automation of data transformation tasks and management of large datasets makes Power Query indispensable for professionals in business intelligence.
The Challenge Presented by JSON Data
In various business environments, JSON (JavaScript Object Notation) serves as a frequent data format, particularly for inter-system data exchange. Although the nested structure of JSON aids in organizing information, it can complicate the use of tools such as Power BI, which typically require data in a flattened table format for proper analysis. Conventional methods often necessitate unique custom coding for different JSON structures, leading to time consumption and maintenance difficulties.
To overcome this, we developed a Power Query approach that dynamically translates any JSON data into a fully expanded table format. This blog outlines how the solution operates, shares the relevant code, and elucidates the main logic behind it.
The Issue: Dealing with Diverse JSON Structures
JSON files frequently include nested records and collections, complicating efforts to convert them into a table format that is easily usable. Writing different code for each variation of JSON data becomes both tedious and prone to mistakes, particularly when dealing with information from various sources or systems.
Our aim was to devise a solution capable of accommodating any JSON structure, autonomously transforming it into a flat table without the necessity for ongoing code modifications.
The Resolution: Dynamic JSON Parsing via Power Query
We established a two-part Power Query script-JsonParser and ColumnExpander-to dynamically parse and expand JSON data into tabular format. Below is a concise description of each script's functionality:
JsonParser: Loading and Parsing JSON Content
The JsonParser script manages the task of fetching JSON data from a SharePoint site, parsing it, and preparing it for further expansion.
```m let JsonParser = (FileNameWithoutExtension as text) => let JsonFileName = FileNameWithoutExtension & ".json", SharePointFilesList = SharePoint.Files("https://YourSharepointSiteURL/", [ApiVersion = 15]), FileContent = SharePointFilesList{[Name=JsonFileName, #"Folder Path"="https://YourSharepointFolderURL"]}[Content], JsonParsedData = Json.Document(FileContent, 1252), DataAsTable = if JsonParsedData is record then Record.ToTable(JsonParsedData) else if JsonParsedData is list then Table.FromList(JsonParsedData, Splitter.SplitByNothing(), null, null, ExtraValues.Error) else null, ExpandedData = if DataAsTable <> null then ColumnExpander(DataAsTable, 0) else null in ExpandedData in JsonParser ```
Key Points:
File Retrieval and Parsing: The script retrieves the specific JSON file from SharePoint and converts it into tabular form. It adeptly manages both record and list formats.
Initial Expansion Process: Valid parsed data prompts the script to commence the expansion sequence utilizing the ColumnExpander function.
ColumnExpander: Unpacking All Columns
The ColumnExpander script recursively unveils any nested lists or records found within the table, ensuring the complete flattening of JSON data into a table format.
```m let ColumnExpander = (TableToExpand as table, ColumnToExpand as number) => let CurrentColumnIndex = ColumnToExpand, CurrentColumnName = Table.ColumnNames(TableToExpand){CurrentColumnIndex}, CurrentColumnContents = Table.Column(TableToExpand, CurrentColumnName), HasRecords = List.Contains(List.Transform(CurrentColumnContents, each _ is record), true), HasLists = List.Contains(List.Transform(CurrentColumnContents, each _ is list), true), CanExpandColumn = HasRecords or HasLists, IntermediateTable = if CanExpandColumn then if HasRecords then let RecordFieldsToExpand = List.Distinct( List.Combine( List.Transform( CurrentColumnContents, each if is record then Record.FieldNames() else {} ) ) ), NewExpandedColumnNames = List.Transform( RecordFieldsToExpand, each CurrentColumnName & "." & _ ) in Table.ExpandRecordColumn( TableToExpand, CurrentColumnName, RecordFieldsToExpand, NewExpandedColumnNames ) else if HasLists then Table.ExpandListColumn(TableToExpand, CurrentColumnName) else null else TableToExpand, FullyExpandedTable = if CanExpandColumn then ColumnExpander(IntermediateTable, CurrentColumnIndex) else if CurrentColumnIndex = (Table.ColumnCount(IntermediateTable) - 1) then IntermediateTable else ColumnExpander(IntermediateTable, CurrentColumnIndex + 1) in FullyExpandedTable in ColumnExpander ```
Key Points:
Recursive Expansion: This script evaluates each column for nested records or lists and proceeds to expand them as necessary.
Versatility: The script guarantees thorough expansion of columns until every layer of nested data is flattened, irrespective of the depth or complexity inherent in the original JSON format.
Significance of the Solution
This Power Query approach simplifies working with JSON data. Instead of repeatedly writing new code for various data configurations, these scripts allow users to efficiently flatten any JSON structure into tables. This not only saves time but also minimizes errors and enhances the overall data processing workflow.
Conclusion
Transforming JSON data into a workable format can be straightforward. With the JsonParser and ColumnExpander scripts, you can streamline the process of expanding JSON data into tables, empowering you to focus on the critical tasks of data analysis and utilization.
