Forum Discussion
Grouping Data That Does Not Exist In The Table(s)
Hi,
New to Power BI but loving it so far!
I have a question about grouping options in a field in the table.
The data source I am working with is a massive BO data dump from BO and placed into an Excel sheet on my company's sharepoint site. The data is user inputs and the field I am interested in is a drop down in the end user input database. There are many options in the database to choose from but not all have been chosen. I would like to be able to create groupings to include data that does not yet exist in the dataset.
Example:
1, 2, 3, 4, 5, 6, 7, 8, 9, are options that could be in the data set at any given time.
1, 3, 4, 6, 8, currently exist in the dataset.
I want to set up a group that says:
1, 3, 5, 7, 9 = Odd Group
2, 4, 6, 8 = Even group
The data I am working with has more of a naming convention like, K-ABC-001, or K-DEF-002 etc... if that helps with providing an answer to my riddle.
My first inclanation is to create a linked data table with the possible options pointed at the field in my actual data set but not quite sure how to even do that, if that is the answer.
Thanks in advance for any help on this.
Hi Anonymous -
You could make a new Calculated Column in your table, which parses the portion of the string to group on. For example:
Parsed = MID(<TableName>[<ColumnName>], 7, 3)Then, add Parsed to a Slicer visual, if you want to filter a certain value. Or add Parsed to any visual, for that matter.Hope this helps,NathanI think you are headed in the right direction with a separate table. You could do 2 tables, one for odd and one for even or a combined (see below). You then just need to create the relationship with your other table and then use the fields from the new table as your slicer(s).
Value Category
001 Odd
002 Even
003 Odd
004 Even
I think jtownsend21 has a good start for your dimension table. Add as many descriptive attributes as you can think of, including the full code that is being parsed.
So yup....the answer is a combonation.
Just to follow up with what worked in it's entirety for the thread...
I found on the SharePoint where the data table exists of what all the possible options are and fetched that dataset as well.
In that table I parsed the field I needed parsed and grouped as I wanted.
Then I link the two tables together by the appropriate field and ka-boom! My reports show me what I was looking for.
Thanks to both of you guys for leading me down that path!! Awesome forum!
7 Replies
So just to be more clear.... the data I am looking at and want to group looks more like this...
K-ABC-001-DEF-JKL-MNO
or
K-ABC-002-DEF-JKL-MNO
It is the bolded, underlined data that I want to group. The data may or may not be currently in the dataset but has the possibility to be.
I have the list of possible options that could exist in the field.
Hi Anonymous -
You could make a new Calculated Column in your table, which parses the portion of the string to group on. For example:
Parsed = MID(<TableName>[<ColumnName>], 7, 3)Then, add Parsed to a Slicer visual, if you want to filter a certain value. Or add Parsed to any visual, for that matter.Hope this helps,NathanSo I get how to parse the data that currently exists in the table, what I don't understand how to do is create a group to include data that does not currently exist but could.
Im thinking I need to have a seperate linked table with all of the possible data options, parse it in that table and then link that table to the existing data set table?
I feel like Alice at this point.
So yup....the answer is a combonation.
Just to follow up with what worked in it's entirety for the thread...
I found on the SharePoint where the data table exists of what all the possible options are and fetched that dataset as well.
In that table I parsed the field I needed parsed and grouped as I wanted.
Then I link the two tables together by the appropriate field and ka-boom! My reports show me what I was looking for.
Thanks to both of you guys for leading me down that path!! Awesome forum!