Forum Discussion
Help with filtering on multiple delimited columns
I run an email campaign where I need to input a static file into the email system and utilize multiple mail merge fields to present a table to the user with a number of tasks they need to complete.
The issue we have is the system is not intelligent enough to detect there are multiple rows and transpose them. I know I can group the email column and then delimit the remaining columns into the right number of columns that I need for my email/mail merge system, but I do have the added requirement of being able to filter on specific tasks or due dates so not to inundate the user with a bunch of unneeded information. I could create a slicer that filters out the task or due date but I need to other corresponding columns to also filter out. See two examples below (“dataset with task 1 filtered out” compared to “Transposed dataset” and “dataset with duedate 1 filtered out” compared to “transposed dataset”)
Original dataset
Project Tasks | Due Date | |
Task1 | Date1 | |
Task2 | Date2 | |
Task1 | Date3 | |
Task4 | Date1 |
Transposed dataset
Project Tasks.1 | Project Tasks.2 | Project Tasks.3 | Due Date.1 | Due Date.2 | Due Date.3 | |
Task1 | Task2 | null | Date1 | Date2 | null | |
Task1 | Task4 | null | Date3 | Date1 | null |
Dataset with Task1 filtered out
Project Tasks.1 | Project Tasks.2 | Project Tasks.3 | Due Date.1 | Due Date.2 | Due Date.3 | |
Task2 | null | null | Date2 | null | null | |
Task4 | null | null | Date1 | null | null |
Dataset with DueDate 1 filtered out
Project Tasks.1 | Project Tasks.2 | Project Tasks.3 | Due Date.1 | Due Date.2 | Due Date.3 | |
Task2 | null | null | Date2 | null | null | |
Task1 | null | null | Date3 | null | null |
For full context, I have like 9 different original columns that end up being delimited into 30 delimited columns each. It’s a lot but it’s for a mail merge so it’s expected.
Hoping for help on how to get these filters in place! Duplicating the columns before I group causes issues and I’m not sure which way to go.
4 Replies
- amitchandak
Super User
Anonymous , Not very clear. If your dataset is in the format of the Original dataset, where I do not see any delimited column.
You should keep it as , and use matrix visual and slicer as per need
amitchandak sorry it wasn't clear. From the original dataset, I grouped by email, split the text of the remaining columns by a delimiter, then split each of the columns. That got me the transposed dataset.
- v-chenwuz-msft
Community Support
Hi Anonymous ,
Did you use power query eidtor to transpose original dataset? It may be better to use the original dataset as the data table for maritx table visual.
If you select task 1, then only task 1 column will be left.
Or share your pbix file without sensitive data and expect result.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
