How to control variable filters automatically in Power query?

Question

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"

Where do you want user input to come from? – Alejandro Lopez-Lago - MSFT Nov 02 '16 at 18:29 — Alejandro Lopez-Lago - MSFT, Nov 02 '16 at 18:29
@AlejandroLopez-Lago-MSFT : modified the question – Knight Nov 03 '16 at 11:02 — Knight, Nov 03 '16 at 11:02

score 1 · Answer 1 · edited May 23 '17 at 12:08

1

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},

edited May 23 '17 at 12:08

Community

1
1

answered Nov 03 '16 at 17:58

Carl Walsh

6,100
2
46
50

Thanks for the solution, Its working with my local datasets, but when I'm using SSAS cube data in power query, it shows empty rows. I used (Cube.AttributeMemberId([Dashboard_all.Country]) = "[Dashboard_all].[Country].&[UserInput]" meta [DisplayName = "UserInput"])) in – Knight Nov 06 '16 at 16:04
@Maddy great you got it working. For SSAS query, make sure you're using `UserInput` as a variable, and not `"UserInput"` as a string. – Carl Walsh Nov 06 '16 at 16:17
I tried both ways, but it doesn't work, table shows empty .......................... Cube.AttributeMemberId([Dashboard_all.Country]) = "[Dashboard_all].[Country].&[UserInput]" meta [DisplayName = UserInput])) – Knight Nov 07 '16 at 10:01
The code `"[Dashboard_all].[Country].&[UserInput]"` looks wrong. Why don't you ask a new stack overflow question showing the working ssas query, what you tried, and what you got! – Carl Walsh Nov 07 '16 at 14:50

Eugene · Answer 2 · 2016-12-03T21:34:26.693

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.

How to control variable filters automatically in Power query?

2 Answers2