Power Query - separating a column into multiple columns

Question

Hi,&nbsp;Currently I have multiple data points located within one row cell in one column (please refer to the 'Current data' part in the snippet).&nbsp;In the Power Query Editor I'd like to transform this one 'AllData' column into 5 different columns (as per 'How the data should look like' example).&nbsp;Problem sample in Excel:&nbsp;Actual data in Power BI:&nbsp;I've tried multiple scenarios, where I can extract certain data successfully, but not all of it.&nbsp;Many thanks in advance,&nbsp;Martynas&nbsp;

spinfuzer · Answer

sam_gift&nbsp;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.&nbsp; Records look like&nbsp;[ column name = value, column name 2 = value2 ].&nbsp; However, since some of the values had commas in them, I had to put all the values around quotes in the example.&nbsp; I replaced line feeds #(lf) with commas and then a line feed to separate each field of the record.&nbsp; You may need to experiment with #(cr) as well.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;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 _ &amp; """", type text}}),
    #"Added Custom" = Table.AddColumn(#"Added Suffix", "Records", each Expression.Evaluate("[" &amp; [AllData] &amp; "]")),
    #"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"&nbsp;&nbsp;&nbsp;&nbsp;

halfglassdarkly · Answer

Hi, if your data points always appear in the same order then you should be able to use Split Column by Delimiter with&nbsp;#(lf) as a custom delimiter. That will split your column value using the line feed as the delimiter.

martynasbi · Answer

Hi&nbsp;halfglassdarkly&nbsp;,&nbsp;I've tried it, but since currently some rows have 3 distinct points (Status, CID and Period as per snippet above) while others have 5 (Status, CID, Period, Docs, Time taken), after the split using custom delimiter, the columns become inconsistent.&nbsp;Please refer to the below of what I am referring to&nbsp;

halfglassdarkly · Answer

One option, you could try splitting the column by #(lf) to rows instead of columns, then use split column by delimiter : to seperate the name of the datapoint before the colon from the datapoint. Then Pivot those two columns.

If you need to retain the datapoint name e.g. Docs: in the body of the datapoint then you'd need to use Add Columns > Extract > Before Delimiter instead of Split Column by Delimiter when performing the second delimit operation.

martynasbi · Answer

Afraid that did not work, perhaps because I have additional columns in the data set? after splitting the column by #(If) to rows instead of columns, no changes occurred&nbsp;

Forum Discussion

Power Query - separating a column into multiple columns

6 Replies

Related content

Split a column into two separate columns

Splitting Multiple Cells consecutive columns into separate rows

Sequential Order column based on multiple criteria

separate values into columns

Conditional formatting multiple columns in matrix separately