Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
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"
Hi, Did you find the solution? I have similar requirement and researching about it
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.
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
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
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
89 | |
88 | |
82 | |
64 | |
49 |
User | Count |
---|---|
125 | |
111 | |
88 | |
69 | |
66 |