Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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"
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 - Power BI Tutorials
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"
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 - Power BI Tutorials
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 |
---|---|
84 | |
77 | |
64 | |
51 | |
46 |
User | Count |
---|---|
101 | |
42 | |
41 | |
39 | |
36 |