Extract invoices

[jazzista]

Hello everybody. I have a table with 2 columns: the firstcolumn has the total count of invoices and the other column has each individualinvoice. I need to create an additional column in order to keep theinvoices that do not end with the word RV (reverse) and RB(rebook) since theyare the valid ones I need.
How can I do this in powerquery? i was trying to use Text. Containsfunction but I am not sure how to use it. Thanks in advance.

 

[Paul_Christie]

Extract Invoices

Please see attached

View attachment extract unique invoices.xlsx

I've split the invoice numbers in to a continuous column using Power Query and then just used an excel filter on the result. I could do the filter using Power Query but I was doing it quickly as my dinner is going cold on the table. Might try again later.

Paul

 

[hossat]

Hi,

You can download this solution. If you have question, just ask it.

Hope this solution will help you.

View attachment extract unique invoices - solution.xlsx


To learn more, visit my personal blog http://numidiabi.wordpress.com

 

[horseyride]

My version attached

let Source = Excel.CurrentWorkbook(){[Name="invoices"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"inv detail", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "inv detail"),
    #"Added Custom" = Table.AddColumn(#"Split Column by Delimiter", "Custom", each if Text.End([inv detail],2)="RV" or Text.End([inv detail],2)="RB" then "ignore" else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Invoices", "Custom"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns")
in
    #"Removed Duplicates"

View attachment extract unique invoices-1.xlsx

 

[jazzista]

Sorry for this late reply. Thakns for all the differenrtes solutions. Really appreciated the helps. Have a great day