Power Query - Merging Multiple Columns and Removing Duplicates and unnecessary data in Columns

[Jamming_Mon]

Hello,

I'm trying to merge multiple invoice columns into one.  Here's what I'm trying to do. 

1.  Remove Duplicate invoices found in all columns

2. Remove certain texts like TBD, Unknown etc and only include the Invoice Number

3. Remove the "INV" in the invoice text to only include the number

4. Merge data into one single Invoice Column.   

Included below is a screenshot of what I'm trying to accomplish.  

I tried exporting all duplicates and texts into excel so that I can just use the remove and replace feature in PBI, I quickly realized that I was looking at thousands of duplicates and texts and this was not reasonable. 

I think I may be able to remove the INV text by spliting column by custom delimiter "INV".  But if anyone knows of a simpler way through power query or trick, that will be really useful.  

Cheers!

[mwegener · Accepted solution]

Hi @Jamming_Mon ,

Power Query:

1. Mark all columns and "Transform" --> "Unpivot Columns"

2. "Home" -->"Choose Colums" select "Value"

3. "Text Filters" on Column "Begins with..." = "INV"

4. "Transform" --> "Extract" --> "Text After Delimiter" = "INV"

5. "Home" --> "Remove Rows" --> "Remove Duplicates"

[Jamming_Mon]

This worked, thanks! 🙂

[amitchandak]

@Jamming_Mon 

Try a new table like

Var _tab =distinct(union(all(Table[Invoice]),all(Table[Invoice]),all(Table[Invoice])))

return
selectcolumns(_tab,SUBSTITUTE("INV",[Invoice]))