Forum Discussion

khaycock's avatar
khaycock
Icon for Helper I rankHelper I
5 years ago
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...
Need Help
  • AlB's avatar
    AlB
    5 years ago

    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.

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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