Forum Discussion

Ayupchap's avatar
Ayupchap
Helper III
8 years ago
Solved

Issue with groups

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 wor...
Need Help
Show and Tell
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    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

RMDNA's avatar
RMDNA
Solution Sage
8 years ago

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's avatar
Ayupchap
Helper III
8 years ago

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's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    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's avatar
      Ayupchap
      Helper III
      8 years ago

      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's avatar
        v-yulgu-msft
        Microsoft Employee
        8 years ago

        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