Forum Discussion

MartynasBI's avatar
MartynasBI
Frequent Visitor
3 years ago

Power Query - separating a column into multiple columns

Hi,   Currently I have multiple data points located within one row cell in one column (please refer to the 'Current data' part in the snippet).   In the Power Query Editor I'd like to transform t...
Need Help
spinfuzer's avatar
spinfuzer
Solution Sage
2 years ago

sam_gift 

The goal is to make it look like a record format and then do an Expression.Evaluate to turn it into a record and expand the records.  Records look like [ column name = value, column name 2 = value2 ].  However, since some of the values had commas in them, I had to put all the values around quotes in the example.  I replaced line feeds #(lf) with commas and then a line feed to separate each field of the record.  You may need to experiment with #(cr) as well.

 

 

 

 

 

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lYyxCsIwFEV/5ZE5Q5vGJZuki9ChpiKUpkPRBwabl1IT/X2DKLg4uN17OfcMAysZZ12cYrop6NKyhDVa0rtawaaQ0lKLqwtnBULsJRv5wMTXweDd4eMH3xtLdThlrDUNB6059KaxdHAeIU5XJAVlVYB39BJX/4h18B4pZvSdSlFZ2lKIF1zzNief9Z9+nOaEbByf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Rows = _t, AllData = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Rows", Int64.Type}, {"AllData", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",": ","= """,Replacer.ReplaceText,{"AllData"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","#(lf)",""",#(lf)",Replacer.ReplaceText,{"AllData"}),
    #"Added Suffix" = Table.TransformColumns(#"Replaced Value1", {{"AllData", each _ & """", type text}}),
    #"Added Custom" = Table.AddColumn(#"Added Suffix", "Records", each Expression.Evaluate("[" & [AllData] & "]")),
    #"Expanded Records" = Table.ExpandRecordColumn(#"Added Custom", "Records", {"Status", "CID", "Period", "Docs", "Time taken", "Comments", "Another Column"}, {"Status", "CID", "Period", "Docs", "Time taken", "Comments", "Another Column"})
in
    #"Expanded Records"