Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Caitlin_Knox
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

12 REPLIES 12
MitieFred
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.

Michelle_Garcia
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?

CahabaData
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?

Phil_Seamark
Employee
Employee

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

 

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?

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.