Forum Discussion
Parse Text from Field
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:[email protected] |
| dao_name:Llllllll Mmmmmmdao_email:[email protected] |
| po_number:9999po_school_name:XXXXpo_phone_number:99999999 |
| po_number:9999po_school_name:XXXXpo_phone_number:99999999 |
| dao_name:Llllllll Mmmmmmdao_email:[email protected] |
| dao_name:Llllllll Mmmmmmdao_email:[email protected] |
| dao_name:Llllllll Mmmmmmdao_email:[email protected] |
| dao_name:Llllllll Mmmmmmdao_email:[email protected] |
| 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:[email protected] |
| dao_name:Llllllll Mmmmmmdao_email:[email protected] |
| dao_name:Llllllll Mmmmmmdao_email:[email protected] |
| po_number:9999po_school_name:XXXXpo_phone_number:99999999 |
| __Saved Cart: BXDr3e |
| dao_name:Llllllll Mmmmmmdao_email:[email protected] |
| po_number:9999po_school_name:XXXXpo_phone_number:99999999 |
| po_number:9999po_school_name:XXXXpo_phone_number:99999999 |
| dao_name:Llllllll Mmmmmmdao_email:[email protected] |
| dao_name:Llllllll Mmmmmmdao_email:[email protected] |
| dao_name:Llllllll Mmmmmmdao_email:[email protected] |
| dao_name:Llllllll Mmmmmmdao_email:[email protected] |
| po_number:9999po_school_name:XXXXpo_phone_number:99999999 |
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
1 Reply
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