Connect Json File to Power BI and Normalize Json File In Power Query

Introduction:

Welcome to our guide on normalizing JSON files using Power Query! JSON (JavaScript Object Notation) has become one of the most popular formats for storing and exchanging data due to its simplicity and flexibility. However, working with JSON data in its raw form can sometimes be challenging, especially when dealing with nested structures or arrays.

In this blog post, we'll delve into the process of normalizing JSON files using Power Query. Normalization refers to the process of organizing data into a tabular format, making it easier to analyze and manipulate.

 

 First we need to import data in power bi desktop with Json connection.

 click on get data inside home tab of power BI Desktop.

 

Click on more tab to get Json file connection.

click on connect button which redirect you to your systeam then choose your Json file.

 

You redirect to your Power Query Editor.

Power Query editor interface like below.

Here we can check Table.column ,Table.Rows are in form of list Not in form of row data that we want.

Click on one of list which show what inside of list.

Here data inside of one  list.

 

It has around 121 rows inside one list and we need to expand it Row wise.

so first idea is to exoand list column by left click on right side of column and choose expand new rows for every column which contains list.

 

But you notice that for one row there are multiple sub-rows which is not appropriate like below.

Here for extract time(First row) only one row valid other rows are not valid or duplicate so these happen with every rows and roes count for table is not acceptable.

so our approach is not valid

here we miss something

In above image(image 5) table.rows and table.column has list in row. if you turn by turn expand both list you notice that both list has data(in some case same rows and in other case rows are not same) with same row count which indicate that both lists data are connected with each other.

so we need to make a new column which combine both list in form of table .

Go to add column tab> select custome column 

it open  custome column interface and write code like below.

Table.FromColumns({[Tables.Columns],[Tables.Rows]})

 it add new column in existing table like below.

when you click on customecolumns row you will redirect to expanded version of table which look like below.

 

now we can normalize data.

now we can separate table to multiple table by for easy for data modeling 

create duplicate table and filter out as needed 

now close your power query tab and you get your desired output

below is advance editor code if you don't want to apply all  steps that we implemented above

let
    Source = Json.Document(File.Contents("C:\Users\MagnusMinds\Downloads\Order_CRHESTHASH_ET_FD2024-03-11_PID184_ORD686_MRK2024-03-08 09 14 44.410_T00638455040948575166.json")),
    #"Converted to Table" = Table.FromRecords({Source}),
    #"Expanded Tables" = Table.ExpandListColumn(#"Converted to Table", "Tables"),
    #"Expanded Tables1" = Table.ExpandRecordColumn(#"Expanded Tables", "Tables", {"Name", "QueryStartTimeUtc", "QueryEndTimeUtc", "QueryElapsedTimeMs", "CurrentRow", "Columns", "Rows", "OriginalRowCount", "Hashes", "MasterRecordIsDuplicate"}, {"Tables.Name", "Tables.QueryStartTimeUtc", "Tables.QueryEndTimeUtc", "Tables.QueryElapsedTimeMs", "Tables.CurrentRow", "Tables.Columns", "Tables.Rows", "Tables.OriginalRowCount", "Tables.Hashes", "Tables.MasterRecordIsDuplicate"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Tables1",{{"Tables.Name", type text}, {"Tables.QueryStartTimeUtc", type datetime}, {"Tables.QueryEndTimeUtc", type datetime}, {"Tables.QueryElapsedTimeMs", Int64.Type}, {"Tables.CurrentRow", Int64.Type}, {"Tables.Columns", type any}, {"Tables.Rows", type any}, {"Tables.OriginalRowCount", Int64.Type}, {"Tables.Hashes", type any}, {"Tables.MasterRecordIsDuplicate", type logical}}),
    #"Expanded Tables.Rows" = Table.ExpandListColumn(#"Changed Type", "Tables.Rows"),
    #"Added Custom" = Table.AddColumn(#"Expanded Tables.Rows", "Customcolumn", each Table.FromColumns({[Tables.Columns],[Tables.Rows]})),
    #"Expanded Customcolumn" = Table.ExpandTableColumn(#"Added Custom", "Customcolumn", {"Column1", "Column2"}, {"Customcolumn.Column1", "Customcolumn.Column2"}),
    #"Expanded Customcolumn.Column1" = Table.ExpandRecordColumn(#"Expanded Customcolumn", "Customcolumn.Column1", {"Name"}, {"Customcolumn.Column1.Name"})
in
    #"Expanded Customcolumn.Column1"