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.
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
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.
Proud to be a Datanaut!
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.
|1||Cox, CenturyLink, Level3|
We are excited to announce the Power BI Super Users!
Overview of Power BI 2020 release wave 2!
Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!
100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.