Split Columns by certain text

[ZachUnger]

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. 

Please and thanks 

Zach

[ZachUnger · Accepted solution]

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?? 

[PijushRoy]

Hi @ZachUnger 

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

After solution

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

[ZachUnger · Accepted solution]

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?? 

[PijushRoy · Accepted solution]

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

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

[ZachUnger]

Perfect 🙂 Thank you so much! 

[Idrissshatila]

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.

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