Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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"
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"
This worked, thanks! 🙂
Try a new table like
Var _tab =distinct(union(all(Table[Invoice]),all(Table[Invoice]),all(Table[Invoice])))
return
selectcolumns(_tab,SUBSTITUTE("INV",[Invoice]))
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |