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 y...
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
wdx223_Daniel
Community Champion
Community Championlet
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