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
ZachUnger
Frequent Visitor

Split Columns by certain text

Hi Team 

 

Me again, 

I'm trying to split this column by PE and CRC codes. I need the full text in new columns. So output would be one column for all PE numbers and another column for all CRC numbers? Is this possible and can some one please help. 

ZachUnger_0-1706471319570.png

Please and thanks 

Zach

2 ACCEPTED SOLUTIONS

Thanks for the quick reply, I am getting the below error when I try and do this. I have been able to create a table but it only gives me a vew results and not all of them?? 

ZachUnger_0-1706480563961.png

 

View solution in original post

Hi @ZachUnger 

Please find the attached File
https://drive.google.com/file/d/1i7PknHFfTH8IQos818_s7IJPjazXgZ0v/view?usp=sharing

 

How to create Blank query
Right click on Left query pane and select Blank query

PijushRoy_0-1706492168694.png
Then go to Advance Editor & paste the code

You are placing code in wrong place. see the attached file


If your requirement is solved, please make sure to MARK AS SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.

Thanks
Pijush
www.MyAccountingTricks.com 
https://www.youtube.com/MyAccountingTricks

 

View solution in original post

5 REPLIES 5
PijushRoy
Super User
Super User

Hi @ZachUnger 

I have created complex example which includes PE & CRC multiple times
Find the problem

PijushRoy_0-1706473960512.png


After solution

PijushRoy_1-1706473988430.png


Create a Blank query in Power Query editor & paste the below code

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCnA1MjUxU9BVcA5yNjAxNDU3UYrVgQpbAIUDXE0tzC0sYYIm5iamEMVGJqYWYFEg28TUHKIWyoAKWcLlEaIWlhCWIVh7LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Please see the Data" = _t]),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Please see the Data", "PE (comments)"}}),
    SplitColumn = Table.SplitColumn(#"Renamed Columns", "PE (comments)", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Split1", "Split2", "Split3"}),
    #"Trimmed Text" = Table.TransformColumns(SplitColumn,{{"Split2", Text.Trim, type text}, {"Split3", Text.Trim, type text}}),
    CombineParts = Table.AddColumn(#"Trimmed Text", "Combined", each {_[Split1], _[Split2], _[Split3]}),
    ExtractCodes = (splitParts as list, prefix as text) =>
        Text.Combine(List.Select(splitParts, each Text.StartsWith(_, prefix)), " - "),
    ExtractPECodes = Table.AddColumn(CombineParts, "PE Codes", each ExtractCodes([Combined], "PE")),
    ExtractCRCCodes = Table.AddColumn(ExtractPECodes, "CRC Codes", each ExtractCodes([Combined], "CRC")),
    RemoveColumns = Table.RemoveColumns(ExtractCRCCodes, {"Split1", "Split2", "Split3", "Combined"})
in
    RemoveColumns

 


Please remove the source section and implement in your production



If your requirement is solved, please make sure to MARK AS SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.

Thanks
Pijush
www.MyAccountingTricks.com 
https://www.youtube.com/MyAccountingTricks

 

Thanks for the quick reply, I am getting the below error when I try and do this. I have been able to create a table but it only gives me a vew results and not all of them?? 

ZachUnger_0-1706480563961.png

 

Hi @ZachUnger 

Please find the attached File
https://drive.google.com/file/d/1i7PknHFfTH8IQos818_s7IJPjazXgZ0v/view?usp=sharing

 

How to create Blank query
Right click on Left query pane and select Blank query

PijushRoy_0-1706492168694.png
Then go to Advance Editor & paste the code

You are placing code in wrong place. see the attached file


If your requirement is solved, please make sure to MARK AS SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.

Thanks
Pijush
www.MyAccountingTricks.com 
https://www.youtube.com/MyAccountingTricks

 

Perfect 🙂 Thank you so much! 

Idrissshatila
Super User
Super User

Hello @ZachUnger ,

 

so you click on the column then click on add columns then extract and then the first one use the text before delimiter to extract the PE , and the delemiter would be the dash     -     then you do the same but text after delimiter to extract CR.

Idrissshatila_0-1706473028424.png

 

Idrissshatila_1-1706473070268.pngIdrissshatila_2-1706473090533.png

check the extract by delimiter
https://learn.microsoft.com/en-us/power-query/split-columns-delimiter

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos
Follow me on LinkedIn linkedIn
Vote for my Community Mobile App Idea

Proud to be a Super User!




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.