Forum Discussion
Data modeling question
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
12 Replies
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! :smileyhappy:
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?
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?
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
