Forum Discussion

ZachUnger's avatar
ZachUnger
Helper II
2 years ago
Solved

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. 

Please and thanks 

Zach

Need Help
Tips and Tricks

5 Replies

  • PijushRoy's avatar
    PijushRoy
    Community Champion
    2 years ago

    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