Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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]))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |