Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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..
did you get a chance to look into this?
@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"
thanks! am i creating a custom column?
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.
Proud to be a Super User!
Hi @mmills2018,
What is the delimeter between the strings, and are they always consistent?
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"
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |