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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Betty888
Helper II
Helper II

Remove delimiter and specific number of caracters before that delimiter

I have a column which is the output of a group by function that looks like :

  1. item 1
  2. Item 2
  3. Items 3
  1. item 1
  2. Item 2
  3. Items 3

 

so, in each cell, I have 2 delimiters: “.” and "#(lf)".

My target is to have rows without the numbering and the "." delimiter : 

item 1

Item 2

Items 3

item 1

Item 2

Items 3

 

Any ideas to help me 🙏

Thanks a lot !

1 ACCEPTED SOLUTION
wdx223_Daniel
Super User
Super User

NewStep=Table.TransformColumns(PreviousStepName,{"column name",each Text.Combine(List.Alternate(Text.SplitAny(_,".#(lf)"),1,1),"#(lf)")})

View solution in original post

4 REPLIES 4
v-yalanwu-msft
Community Support
Community Support

Hi, @Betty888 ;

You could split column by delimiter ; 

vyalanwumsft_0-1666768610389.png

Then delete first column and the final show:

vyalanwumsft_1-1666768650045.png

Or Delete before "." 

vyalanwumsft_2-1666768727014.png

The final show:

vyalanwumsft_3-1666768755276.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtTLLEnNVTBUitWJVjLS8wRxjMAcYzCnWMEYzCNKXSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}}),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Changed Type", {{"Name", each Text.AfterDelimiter(_, "."), type text}})
in
    #"Extracted Text After Delimiter"


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Many thanks ! 

I tried this soltuin, but it didn't work in my case, because the delimiters occur several times ...

Thanks again and have a nice day ! 

wdx223_Daniel
Super User
Super User

NewStep=Table.TransformColumns(PreviousStepName,{"column name",each Text.Combine(List.Alternate(Text.SplitAny(_,".#(lf)"),1,1),"#(lf)")})

Many thanks !

This works magically on my data, all look perfect now !!!!

You saved my day 🙂 

Thank you again ! 

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors