cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Advocate III
Advocate III

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

2017-03-01_13-07-14.png

1 ACCEPTED SOLUTION

@Caitlin_Knox  Let me know if you have any questions...

 

Hope this helps! Smiley Happy

 

Query Editor - Slicer Table.gif

 

View solution in original post

11 REPLIES 11
Helper V
Helper V

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" Smiley Happy

I did and the refresh process works perfectly, grabbing new entries each time.  Thanks for the perfect solution.

New Member

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?

Memorable Member
Memorable Member

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.

 

 

www.CahabaData.com

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?

Microsoft
Microsoft

Using the wonderful Split Columns option in the Query Editor.  It will create new columns which you can then rename.

 

Split Column.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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! Smiley Happy

Slicer Values.png

@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.

 

IDProviderQuoted
1Cox, CenturyLink, Level3
2Cox
3AT&T, Level3
4Wow!, CenturyLink
5inContact, AT&T

@Caitlin_Knox  Let me know if you have any questions...

 

Hope this helps! Smiley Happy

 

Query Editor - Slicer Table.gif

 

View solution in original post

@Sean THANK YOU SO MUCH. This was so easy to follow along, and worked perfect.

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors