Forum Discussion
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 is a gap in the data (row 23 in this first one) and turn it all into one single row of data with different columns before moving onto the next batch and doing the same?
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.
4 Replies
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.
= Table.SplitColumn( Table.FromList( List.Transform(List.Split(PreviousStep[Column1],23), each Text.Combine(List.RemoveLastN(_,1), "/////"))), "Column1",Splitter.SplitTextByDelimiter("/////", QuoteStyle.Csv))
What do I put within PreviousStep?
I would do it this way, by replacing the empty row value with "$" then combining the text, splitting at the "$", then converting the newly split list back into a table. Let's say your last step is currently "LastStep". You can either add each step in the formula editor or paste into the advanced editor (you have to remove your current last comma, and use the "in" statement provided below):
#"Replaced Value" = Table.ReplaceValue(LastStep,"","$",Replacer.ReplaceValue,{"Column1"}),
Custom1 = Text.Combine(#"Replaced Value"[Column1]),
#"Split Text" = Text.Split(Custom1, "$"),
#"Converted to Table" = Table.FromList(#"Split Text", Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"--Nate
