Transform data.

Hi romovaro​,

To complement the PBIX shared above, here is the pattern written out, so you can reuse it on your real file and see why Pivot was failing.

Pivot errors because the values in CEXTRA PARAM1 / PAYLOAD are one JSON text blob per row, not key/value rows. Parse each blob into a record first, then expand the record. Power Query then creates one column per parameter for you.

let
    Source   = Excel.Workbook(File.Contents("C:\path\Book2.xlsx"), null, true),
    Data     = Source{[Item = "Sheet1", Kind = "Sheet"]}[Data],
    Promoted = Table.PromoteHeaders(Data, [PromoteAllScalars = true]),

    // parse both JSON columns; bad/empty rows become null instead of erroring
    Parsed = Table.TransformColumns(Promoted, {
        {"CEXTRA PARAM1", each try Json.Document(_) otherwise null},
        {"PAYLOAD",       each try Json.Document(_) otherwise null}}),

    // collect every key that appears in any row (not just the first one)
    KeysParam   = List.Distinct(List.Combine(List.Transform(
                    List.RemoveNulls(Parsed[#"CEXTRA PARAM1"]), Record.FieldNames))),
    KeysPayload = List.Distinct(List.Combine(List.Transform(
                    List.RemoveNulls(Parsed[PAYLOAD]), Record.FieldNames))),

    ExpParam   = Table.ExpandRecordColumn(Parsed, "CEXTRA PARAM1",
                    KeysParam, List.Transform(KeysParam, each "PARAM." & _)),
    ExpPayload = Table.ExpandRecordColumn(ExpParam, "PAYLOAD",
                    KeysPayload, List.Transform(KeysPayload, each "PAYLOAD." & _))
in
    ExpPayload

Two details that matter on real data:

collecting keys from ALL rows -> new parameters appear as columns on refresh
  (the UI expand button hard-codes only the keys it saw in the preview)
prefixes "PARAM." / "PAYLOAD." -> no clashes when both blobs share a key name
nested objects/arrays -> those columns show Record/List
  -> expand them again the same way, or Text.Combine a list into one cell

If the values look like key=value;key=value rather than JSON, swap Json.Document for a small splitter:

each Record.FromList(
    List.Transform(Text.Split(_, ";"), (kv) => Text.AfterDelimiter(kv, "=")),
    List.Transform(Text.Split(_, ";"), (kv) => Text.BeforeDelimiter(kv, "=")))

So my overall approach would be:

Json.Document (or key=value splitter) -> record per row
-> gather keys across all rows -> Table.ExpandRecordColumn
-> prefix column names -> set data types -> analyze

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.