Learn how to efficiently convert any JSON data into a fully expanded table format using Power Query, reducing the need for custom code and streamlining your data processing.
JSON (JavaScript Object Notation) is a lightweight data-interchange format that is easy for humans to read and write and easy for machines to parse and generate. It’s widely used in web services and APIs to exchange data between clients and servers. JSON’s structure typically includes nested arrays and objects, making it flexible for various types of data but also challenging to work with when it comes to data transformation and analysis.
Power Query is a data connection technology that enables you to discover, connect, combine, and refine data across a wide variety of sources. It is integrated into Microsoft products like Excel and Power BI, providing users with a powerful tool to transform and prepare data for analysis. Power Query’s ability to automate data transformation tasks and handle large datasets makes it an essential tool for business intelligence professionals.
JSON (JavaScript Object Notation) is a common data format, especially in business contexts where data is exchanged between systems. While JSON’s nested structure is useful for organizing data, it can be challenging to work with tools like Power BI, where a flat table format is often required for analysis. Traditionally, handling varied JSON structures involves writing custom code for each case, which can be time-consuming and difficult to maintain.
To address this, we developed a Power Query solution that dynamically converts any JSON data into a fully expanded table format. This blog explains how the solution works, provides the code, and highlights the key logic behind it.
JSON files often contain nested records and lists, making it difficult to transform them into a usable table format. Manually writing different code for each variation of JSON data can be tedious and error-prone, especially when dealing with data from multiple sources or systems.
Our goal was to create a solution that could handle any JSON structure, automatically expanding it into a flat table without the need for constant code adjustments.
We created a two-part Power Query script—JsonParser and ColumnExpander—that dynamically parses and expands JSON data into a table. Here’s a brief overview of how each script works:
The JsonParser script is responsible for loading the JSON data from a SharePoint site, parsing it, and preparing it for expansion.
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
The ColumnExpander script is designed to recursively expand any nested lists or records in the table, ensuring that the JSON data is fully flattened into a table format.
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
This Power Query solution simplifies the process of working with JSON data. Instead of writing new code for each variation in your data, you can use these scripts to automatically flatten any JSON structure into a table. This saves time, reduces errors, and streamlines your data processing workflow.
Transforming JSON data into a usable format doesn’t have to be complicated. With the JsonParser and ColumnExpander scripts, you can automate the process of expanding JSON data into a table, allowing you to focus on what really matters—analyzing and using your data.
If your business is dealing with complex JSON data and needs a reliable, scalable solution, contact us today to learn how we can help you streamline your data processing workflows.
The solution suggested above is designed to dynamically transform JSON data of varying structures into a flat, table format using Power Query. It automates the expansion of nested records and lists within the JSON, eliminating the need for manual adjustments or custom coding for each new JSON structure.
Traditional JSON processing often involves writing specific code for each unique JSON structure, which can be time-consuming and prone to errors. This solution, however, is flexible and dynamic, allowing it to handle any JSON structure automatically. It simplifies the process by recursively expanding all nested elements, making the data ready for analysis without the need for manual intervention.
Yes, it can dynamically expand any JSON data, whether it contains nested records, lists, or a mix of both.
Absolutely. Power Query is optimized for handling large datasets efficiently, although performance may vary depending on the complexity of the JSON.
Most methods require manual adjustments for different JSON structures. This solution automates that process, making it more efficient and reducing the risk of errors.
Yes, they can be tailored to meet the specific requirements of your business, whether you need custom naming conventions, additional data transformations, or integration with other systems.
They have the experience and agility to understand what’s possible and deliver to our expectations.
