cancel
Showing results for 
Search instead for 
Did you mean: 
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
RicoZhou
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!






New Animated Dashboard: Sales Calendar


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
Super User
Super User

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
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors