How to use filter for subheadings in Excel file used as data source

[TesterTest]

I have an excel file as a data source.

I need to create a filter on a column which has priority numbers, and the numbers are under the priority categories.

I need to include the categories only in my filter but the other columns in the table should be filtered based on the numbers under that category.

e.g.

Priority column                                Description

Category 1

1                                                          Category 1 one

2                                                          Category 1 two

3                                                          Category 1 Three

Category 2

1                                                          Category 2 One

2                                                          Category 2 Two

Category 3

1                                                          Category 3 One

2                                                          Category 3 Two

3                                                          Category 3 Three

4                                                          Category 3 Four

5                                                          Category 3 Five

Category 4

1                                                          Category 4 One

Category 5

1                                                          Category 5 One

2                                                          Category 5 Two

I want to have the filter like this

Filter (Priority Category)

Category 1

Category 2

Category 3

Category 4

Category 5

If I select Category 5 the table should display ‘Category 5 One’ and ‘Category 5 Two’ in the table and so on.

The number of rows under ‘Priority Category’ are dynamic.

I tried to get the index number of the Priority Category and create a column based on the index limit, filled the column with the ‘Priority Category’ titles. But this doesn’t seem to work as I need to fill all the rows with the related ‘Priority Category’ Title.

Any help will be greatly appreciated!!

Thanks!

[vanessafvg]

The best thing to do is to create a new column and in that column have the value category 1, and then the sequence in a different column like this

see attached.  

[TesterTest]

Thanks vanessafvg for your quick reply!

So you mean the 'Priority Sequence' column shoud be created in 'Excel file' (Data source) or in the PBI?

[vanessafvg]

that really depends on your situation, often transforming the data for reporting is required and that is done on a data platform or power bi.  I have adjusted your sample data in power bi, should be simple enough to do as well, if you can affect the source obviously thats the most ideal, but in most cases people transform what they have in their data platform.  essentially though you should not mix the column you want to filter with another column that just adds a layer of unncessary complexity, its easier to model your data to provide the correct format.

[TesterTest · Accepted solution]

Thank you vanessafvg!!

I would prefer to do the transform on PBI side.

As I have mentioned above I tried to do that by taking index of the Category rows (which are subheadings in the file). But this didn't work.

Would you please share how did you transform and created a separate column for the priority sequence in PBI?

Many thanks for your help!

[TesterTest]

Works like a charm!!!

Thank you very much!!

[vanessafvg]

i included the power bi in my original post, just download it and have a look in power query

but in summary

i duplicated the category column and then remove the sequence and just filled all the values with category 1, or 2, 3 or 4 using a filll down.  have you looked at the power bi i attach and look in power query at the steps.