Issue with groups

[Ayupchap]

HI all hope somone can help I have a large dataset and I was trying to create groups for one of the columns. I could create them in the grouping page as lists fne but when I press OK it shows the working on it spinning wheel message and that never clears, not even after hours.  Is this an issue with the number of groups I was trying to create or maybe its somethign else?  Anyone have any tips on how I could create all the groups I need.  Shoudl I make a few at a time then press edit and then add more groups?  Any help much appreciated or a workaround also would be excellent.

[RMDNA]

When you say group, are you referring to the "Group By" option in the query editor, or the New Group option when selecting a field?

Also, how large is your dataset?

[Ayupchap]

Hi there

New group option is what I meant, when you right click on a selcted field.  The dataset is about 250,000 records pulling from a ODBC linked database table.  

[RMDNA]

I've definitly seen Power BI hang when using the field group option with that many records.

Why not move to a custom column in Query Editor or a calculated column?

[Ayupchap]

Is this possible when I have different names which I need to match together?  Some have 2 books some don't  the assessments I want to match would be like the following

Grade 6 ELA #1 16 Book 1

Grade 6 ELA # 1 16 Book 2

Grade 6 ELA #2 16 Book 1

Grade 6 ELA # 2 16 Book 2

Grade 6 ELA # 3 16

Obviously I want to combine those that say #1 together and #2 together. The final one in the example listed just has to be on its own, no book. There are many assessments like this above for numerous grades etc.  Can a custom column help with this?  I just have no idea how it would.

[Ayupchap]

Is this possible when I have different names which I need to match together?  Some have 2 books some don't  the assessments I want to match would be like the following

Grade 6 ELA #1 16 Book 1

Grade 6 ELA # 1 16 Book 2

Grade 6 ELA #2 16 Book 1

Grade 6 ELA # 2 16 Book 2

Grade 6 ELA # 3 16

Obviously I want to combine those that say #1 together and #2 together. The final one in the example listed just has to be on its own, no book. There are many assessments like this above for numerous grades etc.  Can a custom column help with this?  I just have no idea how it would.

[v-yulgu-msft]

Hi @Ayupchap,

You can add a calculated column as a new group:

NewGroup = RIGHT('Table 1'[Column1],2)

Alternatively, you could create a summarized table to group original records:

table 1_1 =
SUMMARIZE (
    'Table 1',
    'Table 1'[Column1],
    "Column2", AVERAGE ( 'Table 1'[Column2] ),
    "Book", CONCATENATEX ( 'Table 1', 'Table 1'[Column3], "," )
)

Also, you can try this:

table 1_1 =
SUMMARIZE (
    'Table 1',
    'Table 1'[Column1],
    "Column2", AVERAGE ( 'Table 1'[Column2] ),
    "CountBook", COUNT ( 'Table 1'[Column3] )
)

Best regards,

Yuliana Gu

[Ayupchap]

Oh wow thanks so much for this

Onyl issue is the assesment name and the book name is combined in one field unlike the way its displayed on your amazing example, do you think this will mater?

[v-yulgu-msft · Accepted solution]

Hi @Ayupchap,

That case, would you please make the sample data more clear and more readable? Would you please show me the sample table as what I have posted above?

If I understand corrently, value like "Grade 6 ELA #1 16 Book 1" is stored in a signle column, right? I think it won't matter. You can split it to multiple columns.

Column2 = LEFT(Table3[Column1],17)
Column3 = RIGHT(Table3[Column1],LEN(Table3[Column1])-LEN(Table3[Column2]))

Best regards,

Yuliana Gu

[Ayupchap]

Apologies for not making it clear, the example you use here is exactly how I have it, this is really amazing thanks so much!  This totally solves my problem