Excel filter to reflect during import to PowerBI

[RiniMaria]

Hi,

I have my source data in excel which is updated regularly. This table is the data source for my report in PowerBI. I need to refresh my data in PowerBI based on the filters applied to the source file.

For ex:

This is my data in Excel

My data in powerbi after import should contain all the values indicated above and exclude whatever has been filtered out. And when the filter changes, it should reflect that in powerbi after refresh.

Is this possible?

Thanks in advance!

[wdx223_Daniel · Accepted solution]

try this step

1 add a new column in excel file, say the header is "Flag", and input the formula

    =subtotal(103,b2)

2 amend your M code, add a new step

   =Table.SelectRows(PreviousStepName,each [Flag]=1)

then after changing the filters of excel file, save it, then refresh in PBI files.