Forum Discussion
Solved
Splitting data and transposing
I have an email log that I was hoping to feed into an Excel spreadsheet using power query but it's all in one big column with no clear delimeter. Is there any way I can split each section where there...
Hi khaycock
Place the following M code in a blank query to see the steps.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmASTJiASVMwaYYQNgeTFmDSUik2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), col1_ = #"Changed Type"[Column1], out_ = List.Accumulate(col1_, [stateAll={}, stateItem = {}], (state, current)=> [stateAll= if current="" then state[stateAll] & {state[stateItem]} else state[stateAll], stateItem = if current<>"" then state[stateItem] & {current} else {}] ), res_ = if out_[stateItem] = {} then out_[stateAll] else out_[stateAll] & {out_[stateItem]}, res2_ = Table.FromColumns(List.Transform(res_, each {Text.Combine(_, "|")})) //Change here the separator between rows in res2_Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
