Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello,
I'm trying to figure out how to split the column below into the following 6 columns using Power Query.:
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 |
Solved! Go to 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
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.