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 numb...
Need Help
Tips and Tricks
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

 

ZachUnger's avatar
ZachUnger
Helper II
2 years ago

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