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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mmills2018
Helper IV
Helper IV

Parsing out several rows in one cell

Hello,

within my data set, i have a column that has several different rows within the same cell.  any ideas how i can parse out each one of these rows to a new column.  I am specifcally wanting any data value that begins with "DSL" below is an image of my data set.

 

Power BI - Talent Pools.png

9 REPLIES 9
v-rzhou-msft
Community Support
Community Support

Hi @mmills2018 

Could you share a sample with me by your Onedrive for Business? 

And show me the result you want.

This will make it easier for me to understand your requirement.

 

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

unfortunately, i can't because of the data sensitivity.  i have a column (see image below), there are several line items in once cell.  I wanted to only parse out that data that starts with 'DSL', so i want to create another column with the 'DSL' data parsed out..

 

power bi.png

did you get a chance to look into this?

smpa01
Super User
Super User

@mmills2018can you try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcgn2ickrzs9NVShLzClNjcnLL8lILYJwlGJ1opXwyQE1G0J0V8AkK5RiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [String = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"String", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.StartsWith([String],"DSL") then Text.Split([String],"#(lf)") else {}),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each List.First([Custom])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom"})
in
    #"Removed Columns"
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

thanks! am i creating a custom column?

mmills2018
Helper IV
Helper IV

@richbenmintz hello! any update on this?

Hi @mmills2018 ,

 

First step would be to Replace the Carriage Returns with a special Character like a pipe |, then you can filter out the DSL rows, then you can split them.

 

I hope that makes sense.

 



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


richbenmintz
Solution Sage
Solution Sage

Hi @mmills2018,

 

What is the delimeter between the strings, and are they always consistent?



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


The delimited uses special charaters it is a line feed.  The issue i have with using the line feed, is I really only want the values that begin with "DSL" so from the picture below, splitting by a delimiter, some with have DSL in column Talent Pools.1.1 and some will have "L&D" in column Talent Pools 1.1.  I want one column for "DSL"power bi.png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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