Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
MartynasBI
Frequent Visitor

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

 

Problem sample in Excel:

MartynasBI_0-1681907272254.png

 

Actual data in Power BI:

MartynasBI_1-1681907291828.png

 

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

 

Many thanks in advance,

 

Martynas 

6 REPLIES 6
spinfuzer
Super User
Super User

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

 

 

 

spinfuzer_1-1702104309600.png

 

 

 

 

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
Helper I
Helper I

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

halfglassdarkly
Responsive Resident
Responsive Resident

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 

MartynasBI
Frequent Visitor

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

MartynasBI_0-1681908168896.png

 

halfglassdarkly
Responsive Resident
Responsive Resident

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.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors