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

[Syndicate_Admin]

I am given data from a client's website export. The way that wordpress builds this table is that it simply throws the data into SQL. When a record (order) is missing a value, it simply keeps writing, and does not write a null cell. As a result, there are row-by-row shifts with no real pattern.

Linked below is a snippet of data. I'm looking for two important values:
-delivery_date:[####-##-##]

_delivery_time_frame:[a:2:{s:9:\time_from\;s:5:\##:##\;s:7:\time_to\;s:5:\##:##\;}]

https://www.dropbox.com/s/mnmdrx4o9ziziib/power%20query%20help.xlsx?dl=0 

(any cell with "xxx" is censored customer data)

As you can see, these values end up in different columns. 

Can I build a custom column that searches the row for "-delivery_date:" and puts it in its own column? Something like an if Text.Contains[[all columns],"delivery_date:"] then [that particular cell]?

Thank you in advance.

[Syndicate_Admin · Accepted solution]

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

[Syndicate_Admin]

Hi @jlankford ,

Please let us know if @Jimmy801's reply above is helpful.

If it is, please accept his reply as solution to your question so that people who may have the same question can get the solution directly.

If not, please let us know.

Best Regards,

Icey

[Syndicate_Admin]

Hi Jimmy, 

Thank you for the solution! This worked well, but I do have two followups that I'm happy to pose separately if they're not appropriate here. 

1.) Is it possible to perform this calculation step-by-step? I always seek to learn what's driving a solution so that I can apply it to multiple things, and this one is perplexing me. I know it works, but I don't know why it works. 

2.) How might I implement your solution AFTER loading the dataset and performing some calculations? For instance, the data export I get is a csv with"|" as a delimiter. In order to create the columns to present you with the data, I had to run this (forgot to mention this). I'm unsure how to perform your calculation after running other calculations after loading the source data. 

Thank you again, I appreciate everything!

[Syndicate_Admin · Accepted solution]

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

[Syndicate_Admin]

@Jimmy801, @jlankford 

I see a possible problem with this solution - the final table contains only those columns, which are filled in the first record of the source table. (eg. the final table doesnt have :_edit_lock: column, which is in the second row of source table)

In other words, if you are interested in 2 parcticular values, make sure, these 2 values are in the first row. If these values are always there, then there is no problem.

[Syndicate_Admin]

Thank you again - time to journey through this!

[Syndicate_Admin]

Jimmy - It worked and I learned something - thank you so much! The snippet of your code with "Source" - I didn't realize that this was referring to the last line - I thought it was somehow referring to the source. I now visualize what you wrote as a step-by-step M-code like thing, and I can progress through the logic. I appreciate it.