CODEWITHSUNDEEP
excelpowerqueryvba
Knight
Knight

Reputation: 363

How to control variable filters automatically in Power query?

Is there a way to control the filters in selecting values in queries based on the values given

lets say, I have the following power query, data looks like the following

     country          type       value1     value2....
    ----------------------------------------------------------------------
     USA              a1         22         12
     Uk               a2         21         10

Now in power query, I manually filter these values to make like country="USA", type="a1" to get the pivot tables.

How to filter the values automatically using the user inputs in power query?

User inputs:

        country: [textbox] or workbook cell
        type   : [textbox] or workbook cell

Anly help is appreciated, thanks!

PS: My working query looks like the following, 

let
   Source = Csv.Document(File.Contents("C:\Users\axlptl\Desktop\abc.csv"),[Delimiter=",", Columns=4, Encoding=1252, QuoteStyle=QuoteStyle.None]),
   UserInput = Excel.CurrentWorkbook(){[Name="Table1"]}[Content][country]{0},
   #"Promoted Headers" = Table.PromoteHeaders(Source),
   #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"country", type text}, {"type", type text}, {"value1", Int64.Type}, {"value2", Int64.Type}}),
   #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([type] = UserInput))


in
   #"Filtered Rows"

Upvotes: 2

Views: 719

Answers (2)

Eugene
Eugene

Reputation: 1264

You should create names for your cells containing Country and Type parameters. Let say, cell B1 is country, and B2 is type. Go to Formulas/Name Manager and assign them respective names.

Then (assuming your data table is named Table1) you can use following PowerQuery:

let
    Country = Excel.CurrentWorkbook(){[Name="Country"]}[Content][Column1]{0}?,
    Type = Excel.CurrentWorkbook(){[Name="type"]}[Content][Column1]{0}?,
    Table = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Filter = Table.SelectRows(Table, each [country] = Country and [type] = Type)
in
    Filter

Also pay attention to names, they are case-sensitive.

Upvotes: 1

Carl Walsh
Carl Walsh

Reputation: 6949

Try putting 22 in a workbook cell (maybe on another sheet) and choosing from table. Basically, do what this did: Power query & power pivot - empty table and clearing pivot

You'll want your query to have a line something like:

UserInput = Excel.CurrentWorkbook(){[Name="Table1"]}[Content][country]{0},

Upvotes: 1

Related Questions