Parse Text from Field

[Anonymous]

 Hello,

I'm trying to figure out how to split the column below into the following 6 columns using Power Query.:

• po_number
• po_school_name
• po_phone_number
• __Saved_Cart
• dao_name
• dao_email

As you can see, each cell may only include a few of each of the attributes. I don't even know how to start.

I wanted to add that if there is a po, all three po fields will exist and they will always be in the order of number, school name, and then phone number. In fields with dao, it will always list the dao name first and then dao email. If the field contains both dao and po information, the dao information comes first.

I threw in junk values to protect sensitive information.

Also, it appears that there is a carriage return between each item.

Note Attributes

po_number:9999po_school_name:XXXXpo_phone_number:99999999

__Saved Cart: kP5WJL

dao_name:Llllllll Mmmmmmdao_email:HHHHH@lwoid.com

dao_name:Llllllll Mmmmmmdao_email:HHHHH@lwoid.com

po_number:9999po_school_name:XXXXpo_phone_number:99999999

po_number:9999po_school_name:XXXXpo_phone_number:99999999

dao_name:Llllllll Mmmmmmdao_email:HHHHH@lwoid.com

dao_name:Llllllll Mmmmmmdao_email:HHHHH@lwoid.com

dao_name:Llllllll Mmmmmmdao_email:HHHHH@lwoid.com

dao_name:Llllllll Mmmmmmdao_email:HHHHH@lwoid.com

po_number:9999po_school_name:XXXXpo_phone_number:99999999

po_number:9999po_school_name:XXXXpo_phone_number:99999999

po_number:9999po_school_name:XXXXpo_phone_number:99999999

dao_name:Llllllll Mmmmmmdao_email:HHHHH@lwoid.com

dao_name:Llllllll Mmmmmmdao_email:HHHHH@lwoid.com

dao_name:Llllllll Mmmmmmdao_email:HHHHH@lwoid.com

po_number:9999po_school_name:XXXXpo_phone_number:99999999

__Saved Cart: BXDr3e

dao_name:Llllllll Mmmmmmdao_email:HHHHH@lwoid.com

po_number:9999po_school_name:XXXXpo_phone_number:99999999

po_number:9999po_school_name:XXXXpo_phone_number:99999999

dao_name:Llllllll Mmmmmmdao_email:HHHHH@lwoid.com

dao_name:Llllllll Mmmmmmdao_email:HHHHH@lwoid.com

dao_name:Llllllll Mmmmmmdao_email:HHHHH@lwoid.com

dao_name:Llllllll Mmmmmmdao_email:HHHHH@lwoid.com

po_number:9999po_school_name:XXXXpo_phone_number:99999999

[wdx223_Daniel · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKsiPzyvNTUotsrIEAiCvODkjPz8nPi8xN9UqAgiAQgUZ+XmpyMpAQClWJ1opPj44sSw1RcE5sajESiE7wDTcywcskZKYDzHCJwcCFHxzQQAknpqbmJlj5QECDjnl+Zkpesn5uRTooswLlOmmpz9HSpgO9xihZpZzinApMk4dohE9+KOK7mEaCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Note Attributes" = _t]),
    Custom1 = Table.Combine(Table.Group(Source,"Note Attributes",{"n",each let t= Text.Combine([Note Attributes]),a=List.Transform(List.Skip(Splitter.SplitTextByAnyDelimiter({"po_number:","po_school_name:","po_phone_number:","__Saved Cart:","dao_name:","dao_email:"})(t)),Text.Trim),b=List.Transform(List.RemoveLastN(Splitter.SplitTextByEachDelimiter(a)(t)),each Text.Remove(_,":")) in Table.FromRecords({List.Accumulate(List.Zip({b,a}),[],(x,y)=>Record.TransformFields(x,{y{0},each Text.Combine({_,y{1}},"#(lf)")},2))})},0,(x,y)=>Byte.From(Text.StartsWith(y,"po_number:")))[n])
in
    Custom1