Forum Discussion
Splitting data with overlapping values and then collating into a chart
Hi all. I have a Power BI dashboard that pulls in data from Airtable. In our tables (we have about 15), we have a "Responsible" field where you can tag one or more people. It pulls into Power BI like this, for example:
You can see that "Brid" is included in rows with another person and also by themselves.
GOAL: We'd like to see each person's workload, so we need to split the names out, then collate them somehow.
When I pull the data as-is into a chart, it obviously doesn't separate the names by delimiter, and you can see where the overlap is (highlighted in green, and highlighted in yellow). Hence, we're unable to get a quick look at how much each person has on their plate.
When I delimit them and split them into separate columns, this is how it looks (not exactly better than the previous option).
Any idea how I can separate these cells by delimiter and then re-combine the columns to get an accurate count of task by user without creating new rows?
Thanks in advance for any help!
You have to create new rows to do this. Creating new rows will mean your data is in a tabular format which is what you need for analysis.
In Power Query when you split the names by delimiter, in the Advanced options choose to create new rows, rather than columns
Regards
Phil
Thanks for the reply from PhilipTreacy , please allow me to provide another insight:
Hi briwhiz23 ,I created some data:
Here are the steps you can follow：
1. You can create a calculation table containing the data needed for the X-axis of the clustered column chart.
2. Create measure.
Measure = var _vtable= DISTINCT( FILTER( CROSSJOIN( 'Table','Group_Table'), FIND( 'Group_Table'[Group],'Table'[ASSIGEND TO],,BLANK()<>BLANK()))) return COUNTX( FILTER( _vtable,[Group]=MAX('Group_Table'[Group])),[ASSIGEND TO])3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
2 Replies
- PhilipTreacy
Super User
You have to create new rows to do this. Creating new rows will mean your data is in a tabular format which is what you need for analysis.
In Power Query when you split the names by delimiter, in the Advanced options choose to create new rows, rather than columns
Regards
Phil
Thanks for the reply from PhilipTreacy , please allow me to provide another insight:
Hi briwhiz23 ,I created some data:
Here are the steps you can follow：
1. You can create a calculation table containing the data needed for the X-axis of the clustered column chart.
2. Create measure.
Measure = var _vtable= DISTINCT( FILTER( CROSSJOIN( 'Table','Group_Table'), FIND( 'Group_Table'[Group],'Table'[ASSIGEND TO],,BLANK()<>BLANK()))) return COUNTX( FILTER( _vtable,[Group]=MAX('Group_Table'[Group])),[ASSIGEND TO])3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly