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.
I'm having a table of people, each with a value of how many projects they are working on. But the list contains duplicates when two people are working on it together, which is counted seperately.
Ex: There are 3 people a 10 total projects, here is how the list would look.
"Person 1" - 3 projects
"Person 2" - 2 Projects
"Person 3" - 2 Projects
"Person 1 / Person 2" - 2 Projects
"Person 2 / Person 3" - 1 Project
Is there a way to I can split or combine these values, or insert a delimeter of "/" to get a cleaner listing like this?
"Person 1" - 5 Projects
"Person 2" - 5 Projects
"Person 3" - 3 Projects
Solved! Go to Solution.
ok i think it is easy :
- goto query editor
- select "ASsigned Person" column
- under "Split Column" in menu, choose "By Delimiter"
- Choose semicolor delimiter
- Click Advaced option and select "rows"
- click ok
and this will do, so in you graph, now you will get the data what you are looking for
- drop person on x axis and project id on value (and choose count as summarization)
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
are quotes (") part of your data or it is just put in sample.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Just a sample to show that Person 1 / Person 2 is together.
can you share how your real data looks like?
Also do you always maximum 2 person like Person 1 / Person 2 or it can be more than 2 as well?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Sure, here is the table I'm working on in Direct Query.
Here are the charts that I'm trying to work with, with the top chart showing what I get, and the bottom filtered to show what I'm trying to make (with incorrect counts). But having trouble getting the values to correctly allocate to the individual persons.
ok i think it is easy :
- goto query editor
- select "ASsigned Person" column
- under "Split Column" in menu, choose "By Delimiter"
- Choose semicolor delimiter
- Click Advaced option and select "rows"
- click ok
and this will do, so in you graph, now you will get the data what you are looking for
- drop person on x axis and project id on value (and choose count as summarization)
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Thanks, I think I'm getting slightly closer. I don't seem to have a "rows" option, this might due to not having the last couple updates.
It did split well into additional columns, I'm hopin to maybe add these together on my chart axis, but it looks like right now it is still filtering by the hierarchy.
i think you missed one step there , you need to choose Rows in advanced than columns
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
this is what i produced from you data, assuming there / delimiter where more than 1 people working on a project.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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 |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |