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
– Alejandro Lopez-Lago - MSFT

2016-11-02 18:29:36 +00:00
Commented Nov 2, 2016 at 18:29 — Alejandro Lopez-Lago - MSFT
– Alejandro Lopez-Lago - MSFT, Commented Nov 2, 2016 at 18:29
@AlejandroLopez-Lago-MSFT : modified the question

Knight
– Knight

2016-11-03 11:02:02 +00:00
Commented Nov 3, 2016 at 11:02 — Knight
– Knight, Commented Nov 3, 2016 at 11:02

Community · Accepted Answer · 2017-05-23 12:08:41Z

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, 2017 at 12:08

CommunityBot

11 silver badge

answered Nov 3, 2016 at 17:58

Carl Walsh

7,2173 gold badges53 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Knight Over a year ago

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

Carl Walsh Over a year ago

@Maddy great you got it working. For SSAS query, make sure you're using UserInput as a variable, and not "UserInput" as a string.

Knight Over a year ago

I tried both ways, but it doesn't work, table shows empty .......................... Cube.AttributeMemberId([Dashboard_all.Country]) = "[Dashboard_all].[Country].&[UserInput]" meta [DisplayName = UserInput]))

Carl Walsh Over a year ago

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!

Eugene · Accepted Answer · 2016-12-03 21:34:26Z

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.

Collectives™ on Stack Overflow

How to control variable filters automatically in Power query?

2 Answers 2

4 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Linked

Related