Forum Discussion

RiniMaria's avatar
RiniMaria
Frequent Visitor
3 years ago
Solved

Excel filter to reflect during import to PowerBI

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!

Need Help
Show and Tell
Tips and Tricks
  • wdx223_Daniel's avatar
    wdx223_Daniel
    3 years ago

    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. 

     

1 Reply

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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.