Unable to parse or split JSON values for multiple rows in Power BI

[ahmedalam]

Hi Expert,

I need to split the JSON column values but failed.

Sample data in a Table:

[{"BAKH_BRANCH":"01","BAKH_COSTCENTER":"0501","BAKH_DEPARTMENT":"05","BAKH_EMPLOYEE":"1887","BAKH_EXPTYPE":"AD","MAINACCOUNT":"630010"}]

[{"ASHL_BRANCH":"01","ASHL_COSTCENTER":"Customer Support","ASHL_DEPARTMENT":"07","ASHL_EMPLOYEE":"A-2004","ASHL_EXPTYPE":"OP","MAINACCOUNT":"630130"}]

[{"GINA_BRANCH":"JED","GINA_DEPARTMENT":"6","GINA_EMPLOYEE":"9999","MAINACCOUNT":"630130"}]

[{"BAHO_COSTCENTER":"10","BAHO_DEPARTMENT":"01","BAHO_EMPLOYEE":"BH0055","MAINACCOUNT":"630130"}]

Advance Editor:

let
// Step 1: Source - Replace this with your actual source if needed
Source = GeneralJournalAccountEntryBiEntities,

// Step 2: Parse JSON
ParsedJson = Table.AddColumn(Source, "Parsed", each Json.Document([LedgerDimensionValuesJson])),

// Step 3: Extract the first record from the list
ExtractedRecord = Table.AddColumn(ParsedJson, "Record", each ParsedJson{0}?[Parsed]{0}),

// Step 4: Expand the record to columns
Expanded = Table.ExpandRecordColumn(ExtractedRecord, "Record", Record.FieldNames(ExtractedRecord{0}[Record])),

// Step 5: Extract Employee
AddEmployee = Table.AddColumn(Expanded, "Employee", each
try Record.FieldValues(Record.SelectFields(_, List.Select(Record.FieldNames(_), each Text.EndsWith(_, "_EMPLOYEE")))){0}
otherwise null
),

// Step 6: Extract Department
AddDepartment = Table.AddColumn(AddEmployee, "Department", each
try Record.FieldValues(Record.SelectFields(_, List.Select(Record.FieldNames(_), each Text.EndsWith(_, "_DEPARTMENT")))){0}
otherwise null
),

// Step 7: Keep only needed columns
Final = Table.SelectColumns(AddDepartment, {"Employee", "Department"})
in
Final

End result:

Problem:

All the rows data should be according to the Sample data.

[ronrsnfld]

Here is another way of extracting the EMPLOYEE and DEPARTMENT records from each json line:

let
    Source = "REPLACE WITH YOUR ACTUAL DATA SOURCE",
    #"Transformed Column" = Table.TransformColumns(Source, {"Column1", Json.Document}),

//Expand the column of Records
    #"Expanded Column1" = Table.ExpandListColumn(#"Transformed Column", "Column1"),

//Select the relevant records
    #"Select Records" = List.Accumulate(
        #"Expanded Column1"[Column1],
        {},
        (s,c)=> s & {[a=List.Transform(Record.FieldNames(c), each {_, List.Last(Text.Split(_,"_"))}),
                 b=Record.RenameFields(c,a),
                 x=Record.SelectFields(b,{"EMPLOYEE","DEPARTMENT"})][x]}),

//Convert to Table and Expand
    #"Converted to Table" = Table.FromList(#"Select Records", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column2" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"EMPLOYEE", "DEPARTMENT"})
in
    #"Expanded Column2"

Results from your posted data set:

[DataNinja777 · Accepted solution]

Hi @ahmedalam ,

The problem in your M code is that you're creating a static reference that always points to the first row of your table. Your line each ParsedJson{0}?[Parsed]{0} uses ParsedJson{0} to grab the data from the very first row, so when the query iterates, it applies that same first row's data to every subsequent row. This is why you see the employee 1887 and department 05 repeated.

To fix this, you need a query that processes each row's JSON data individually. You can replace your existing script in the Advanced Editor with the following corrected M code. This version is more efficient and correctly handles the dynamic keys in your JSON, such as BAKH_EMPLOYEE and ASHL_EMPLOYEE, for each distinct row.

let
    Source = GeneralJournalAccountEntryBiEntities,
    ExtractValues = Table.AddColumn(Source, "ExtractedData", each
        let
            record = Json.Document([LedgerDimensionValuesJson]){0},
            employeeKey = List.SingleOrDefault(List.Select(Record.FieldNames(record), each Text.EndsWith(_, "_EMPLOYEE"))),
            departmentKey = List.SingleOrDefault(List.Select(Record.FieldNames(record), each Text.EndsWith(_, "_DEPARTMENT"))),
            employeeValue = if employeeKey <> null then Record.Field(record, employeeKey) else null,
            departmentValue = if departmentKey <> null then Record.Field(record, departmentKey) else null
        in
            [Employee = employeeValue, Department = departmentValue]
    ),
    ExpandColumns = Table.ExpandRecordColumn(ExtractValues, "ExtractedData", {"Employee", "Department"}),
    FinalTable = Table.RemoveColumns(ExpandColumns, {"LedgerDimensionValuesJson"})
in
    FinalTable

This corrected code works by first adding a temporary column named ExtractedData. For each row, it parses the JSON string and immediately extracts the single record from the list using Json.Document([LedgerDimensionValuesJson]){0}. This is the crucial change, as it operates within the context of the current row. It then safely finds the keys ending with _EMPLOYEE and _DEPARTMENT using List.SingleOrDefault, which prevents errors if a key is missing. Finally, the Table.ExpandRecordColumn function takes the ExtractedData record and splits it into the final Employee and Department columns you need, giving you the correct values for every row.

Best regards,

[ahmedalam]

Hi @DataNinja777 

Thanks for your quick help. It resolve my issue.

Big kudos to you.

[rohit1991]

Hi @ahmedalam 

The error occurs because Power BI can’t automatically split JSON values into multiple rows. You need to parse the JSON first, then expand only the parts you need. Here’s how you can do it step by step in Power Query:

1. Load your source table into Power Query.

2. Parse the JSON column using:

ParsedJson = Table.AddColumn(Source, "Parsed", each Json.Document([YourColumnName]))

3. Expand the parsed values into records and then into columns.

Expanded = Table.ExpandRecordColumn(ParsedJson, "Parsed", {"BAHD_EMPLOYEE","BAHD_DEPARTMENT"})​

4. Select only the needed fields (Employee and Department).

Final = Table.SelectColumns(Expanded, {"BAHD_EMPLOYEE", "BAHD_DEPARTMENT"})

This will give you a clean table where each row contains the Employee and Department values, matching the structure from your sample data.

[ahmedalam]

Hi @rohit1991 

Thanks for response.

I have an issue, that all the rows has different JSON structure.

Well, I have tried what you have explained but it's not working.

[rohit1991]

Hi @ahmedalam 

Could you please try using a dynamic approach in Power Query since each row has a different JSON structure.

• First, parse the JSON column with Json.Document.

• Then convert each row into a list of records so both objects and arrays are handled.

• Collect all possible field names across rows.

• Finally, expand them, letting missing fields return as nulls.

This way, you don’t rely on one fixed schema, and your table will stay consistent even when the JSON structure changes.