Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I'm hoping someone can at least point me in the right direction to do more research. I have a field in my data set that technically a single line text, but has multiple values separated by commas. How can I extract that information and use it in a meaningful way in my report visualizations. Specifically, I woudl like to use it a slicer since it lists Provider names. Here's a screenshot of what the column looks like
Solved! Go to Solution.
Hi Sean,
We have just set up a column in MSProject, within the Projects table, for Systems Impacted. At present only 5 projects have added details of systems impacted, but there are 5 possible systems so the column could have up to 120 variations.
Does your solution (the only one I've found to do what I need) mean that each time a new project is added or an additional impact is identified, we will need to go through the whole process of splitting/un-pivoting etc ?
Data so far
Project 1 - Systems affected - Alpha
Project 2 - Systems affected - Alpha, Beta
Project 3 - Systems affected - Beta, Gamma, Delta
Project 4 - Systems affected - Gamma, Delta
Project 5 - Systems affected - Alpha, Delta
Regards
Fred
Hi Sean,
As they say, "suck it and see"
I did and the refresh process works perfectly, grabbing new entries each time. Thanks for the perfect solution.
If i have a Report connected with One Drive, how i can modeling the data??? I have one fiel as date in the original file but when i use it in Power BI it looks like text how do i can change it?
If I assume that you wish to slice on each of those phrases (between commas) - then I'll add to the other replies already posted: your modeling is several steps.
You will first use the split column feature - to set them in their own columns
but then you need to create a new table with your unique record ID and just those columns - and then make that UnPivoted so it is table by itself that has ID in each record and 1 phrase in each record.
This final table needs to be joined back to the original table and is the 'Many' table of a 1:Many relationship.
The unique record identifier is just a number (ID) as its an OData connection to a SharePoint Online list. So I just use the default ID created when a new item is added to the list.
In order to create the new table with the unique record identifier and (now) 5 columns, do I just multi select and then...?
If the answer is just copying and pasting, how does that second table get refreshed when new items are added?
Using the wonderful Split Columns option in the Query Editor. It will create new columns which you can then rename.
So, that would create 5 columns..Which do I use when creating the slicer? And how would thatrelate back to the rows?
Can you post sample data of this column and what your Unique Row Identifier looks like?
@Caitlin_KnoxBasically this is how you can do this...
If something is unclear just ask!
@Sean I think I did this right. I don't know how to do it in PBI Desktop, so I just created a table in Excel. I didn't see an attach file option, so I just pasted it here.
ID | ProviderQuoted |
1 | Cox, CenturyLink, Level3 |
2 | Cox |
3 | AT&T, Level3 |
4 | Wow!, CenturyLink |
5 | inContact, AT&T |
What if I need the count to add up all the time?
Lets say there are 400 respondents, but for a question, only 300 have answered. If I show the result some people would start arguing that there is something wrong with the data since it is missing 100 respondents.
I would say, they are not missing they did not answer the question. But if I wants it to add up to 400, showing the 100 respondents as 'Did not respond' in the legend. How to do that?
User | Count |
---|---|
103 | |
88 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |