Power Query - separating a column into multiple columns

[MartynasBI]

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 this one 'AllData' column into 5 different columns (as per 'How the data should look like' example).

Problem sample in Excel:

Actual data in Power BI:

I've tried multiple scenarios, where I can extract certain data successfully, but not all of it.

Many thanks in advance,

Martynas 

[spinfuzer]

@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"

[sam_gift]

Hi, Did you find the solution? I have similar requirement and researching about it

[halfglassdarkly]

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]

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 

[MartynasBI]

Hi @halfglassdarkly ,

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. 
Please refer to the below of what I am referring to

[halfglassdarkly]

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