Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Jamming_Mon
Frequent Visitor

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

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.  

 

Invoice Duplicates Power Query.JPG

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!

 

1 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"

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


View solution in original post

3 REPLIES 3

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"

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


This worked, thanks! 🙂

amitchandak
Super User
Super User

@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]))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.