locked
Data Extraction RRS feed

  • Question

  •  I have a description column with A/C and there are some that are just AC. We've already extracted the data related to the A/C hit, but need to extract the data related to the AC (without the slash) hit as well. This has been an issue as there are words with the letters 'ac' in the middle (i.e., attached). There are additional words other than "attached" so it isn't feasible for me to exclude that one word to make the extraction easier. So, the extractions I've performed are also searching those words and returning results for everything in the column with AC. How can I extract only the AC part of the cell data that is NOT in the middle of a word or phrase? I've added the AC to the hit list (Review Items) that was created in the first extraction. Here is my syntax:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Voucher", type text}, {"DO", type text}, {"CLIN", type text}, {"QTY. INVOICE", type number}, {"UNIT OF MEASURE", type text}, {"UNIT PRICE", type number}, {"AMOUNT", type number}, {"Description ", type text}}),
        #"Uppercased Text" = Table.TransformColumns(#"Changed Type",{{"Description ", Text.Upper, type text}}),
        #"Replaced Value" = Table.ReplaceValue(#"Uppercased Text",null,"No Description",Replacer.ReplaceValue,{"Description "}),
        #"Invoked Custom Function" = Table.AddColumn(#"Replaced Value", "ReadString", each ReadString([#"Description "], ReviewItem)),
        #"Expanded ReadString" = Table.ExpandTableColumn(#"Invoked Custom Function", "ReadString", {"SearchItems"}, {"SearchItems"}),
        #"Filtered Rows" = Table.SelectRows(#"Expanded ReadString", each ([SearchItems] = "AC")),
        #"Inserted Text After Delimiter" = Table.AddColumn(#"Filtered Rows", "Text After Delimiter", each Text.AfterDelimiter([#"Description "], "AC"), type text),
        #"Trimmed Text" = Table.TransformColumns(#"Inserted Text After Delimiter",{{"Text After Delimiter", Text.Trim, type text}}),
        #"Extracted First Characters" = Table.TransformColumns(#"Trimmed Text", {{"Text After Delimiter", each Text.Start(_, 0), type text}}),
        #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Extracted First Characters", {{"Text After Delimiter", each Text.BeforeDelimiter(_, ", "), type text}}),
        #"Sorted Rows" = Table.Sort(#"Extracted Text Before Delimiter",{{"Text After Delimiter", Order.Ascending}}),
        #"Renamed Columns" = Table.RenameColumns(#"Sorted Rows",{{"Text After Delimiter", "AC #"}})
    in
        #"Renamed Columns"

    Thursday, November 5, 2020 4:09 PM

All replies

  • If you're looking for AC as a standalone string, then including spaces in the searchstring should do the job: " AC ".
    Other options are checking for Text.Length, Text.StartsWith or Text.EndsWith.



    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Sunday, November 15, 2020 8:00 AM