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

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,