Expected record value resides in random columns. Each row has value listed in different column.

Hello jlankford 

check out this solution. It converts your cell value into a record using ":[" as splitter. Then builds it up again into a readable table.

Just change the source step with your table. The code is working dynamically. Only request is to have a splitter of ":[" in your cell values and a "]" at the end.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1ZRbb9owFMe/C8+t5ISEgPfUTRUMyqr1MjUNyHJtDzzi2LMdElTtu88YdpOgpYWq64sl+9x8fv4fZ1mDGzTHFrGaCWVhVshx46iBKMv5nOkFotgymIUgBMcgOgbBv1bLBUNfNRbOB8MQ3hvYgaPR+liK0eidgbE7CUIIgN8lv+xW/rG2V9YfPruZcqV4MdlSW1ZFLjFFimnBjeGyMGiicWEZhdmCGe+lGZGaMooMzpnZcE5kqWSBSoMnbLkp7F9eSxeNjJVk5iJoSX6nHh9lW+EEz4YTPQgngs1H4axrVwSZ7yXWriV3b24RwZq6Fqy7jm8SZk3v6I256w9mQdxuteImAAH0STYLYnNm67gbxB0dJvsims17HXY50DNlb8iX/u03m15ck9vUZ/C+mFj3YLtELEnXde0iV+uTdPo2ngLsRLZ1IlLVu8C0JvpkWA7Ih/PiCpgi6L7fn1P8XE7JisQe87yqLTDPyZQLhSopiRSCacKQa8KUd4ZofrccvacQ3Y9H8jK62YlHcljdiMtZ96OepAvVt9G8+3lQNc961U1ydv5pk262Imm/IpL24SWy7Y9747r5v+YoDF/vX1nXPtgcRfH0agqG/aFNa2Vbgp0KRlUnV9W1n6PxTw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Custom Fields.39" = _t, #"Custom Fields.40" = _t, #"Custom Fields.41" = _t, #"Custom Fields.42" = _t, #"Custom Fields.43" = _t, #"Custom Fields.44" = _t, #"Custom Fields.45" = _t, #"Custom Fields.46" = _t]),
    TransformRow = Table.FromRecords
    (
        List.Transform 
        (
            Table.TransformRows
            (
                Source,
                (rec)=> Record.ToTable(rec)
            ),
            (tbl)=> Record.Combine
            (
                Table.TransformColumns
                (
                    tbl,
                    {
                        {
                            "Value",
                            (recordvalue)=>
                            let 
                                Splitby = Text.Split(recordvalue, ":["),
                                RecordName = Splitby{0},
                                RecordValue = Text.Start(Splitby{1},Text.Length(Splitby{1})-1),
                                CreateRecord= try Record.AddField([],RecordName, RecordValue) otherwise []
                    
                            in   
                                CreateRecord
                        }
                    }
                )[Value]
            )
            
        ),
        null,
        MissingField.UseNull
    )
    
in
    TransformRow

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

Hello jlankford 

about your questions

1. i can write you the approach, what I used, so you can maybe follow better. First step is to transform the table into Rows using Table.TransformRows and using the function Record.ToTable. This creats a list where one item is a row transformed into a table. Then i use List.Transform to go through this list of tables and apply a Table.TransformColumns to change value column (your cell content). On this i apply a Text.Split to separate your column name and the cell value and create a new record. After this table contains in the column "value" your new records. Then i use Record.Combine to put all records created into on record... So you get a list where every item is you new structured row. Then I use Table.FromRecords to put them into a table again.

2. my solution is dynamic. so just register your data access, copy paste my step "TransformRow" into your code and change the variable "Source" in the function Table.TransformRows to your last step. Maybe "changes type". Don't forget to put the Step-name "TransformRow also after your in-keyword

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy