cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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.

4 ACCEPTED SOLUTIONS
Anonymous
Not applicable

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,
Nathan

View solution in original post

I 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

View solution in original post

Anonymous
Not applicable

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.

View solution in original post

Anonymous
Not applicable

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!

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

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.

Anonymous
Not applicable

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!

View solution in original post

Anonymous
Not applicable

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,
Nathan

View solution in original post

Anonymous
Not applicable

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

Anonymous
Not applicable

@Anonymous - Yes, it's always a good idea to separate "Dimensions" from "Fact Tables". If you're not familiar with "Dimensional Modeling", it would be good to get acquainted with that. 

 

 

I 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

View solution in original post

Anonymous
Not applicable

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.

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!