How to Group Data, my "helper" table is breaking the Sharepoint refresh

[electrobrit]

My project is help desk tickets.

This list is shortened but status can be Closed, Cancelled, Open, Pending, Waiting Customer Response, Awaiting Vendor 

THe last four still all fall into  "Open" so would like them to show by OPEN, CLOSED and CANCELLED then when you drill down into open you see more detail of the tickets that are open(such as pending, awaiting vendor, etc)

After research it seems the only solution is to create a "helper" table as someone called it so I could relate/map the sub status to the Status Categories.  seemed like it could be solved much easier but I couldn't find the solution.

I created a table in excel, put it into my OneDrive, new data source is excel, merged queries, selected my column to use in new query and it worked.

Problem now is my report which is connected to a sharepoint did not update. I tried to manually refresh and this is the error I got.

I deleted my merge query steps to see if it was the new table and it was, I could refresh after disassociating the table.

What am I doing wrong? any way to do groups without the table or if the table is the right thing to do, any help is appreciated.

I have no idea what this error message means.

Thanks in advance!

[v-ljerr-msft · Accepted solution]

Hi @electrobrit,

According to your description, you should be able to create a new calculate column representing for the new status in your existing table, then create a hierarchy with the new created calculated column and your status column, and show the hierarchy on the report in this scenario. See my sample below.

I assume you have a table called "Table1" like below.

1. Use the formula below to create a new calculate column.

Status2 = 
IF (
    Table1[Status] = "Pending"
        || Table1[Status] = "Waiting Customer Response"
        || Table1[Status] = "Awaiting Vendor",
    "Open",
    Table1[Status]
)

2. Create a hierarchy with the new created calculated column and your status column.

3. Show the hierarchy on the report with drill down for "Open".

Here is the sample pbix file for your reference.

Regards