Input Variables to extract Data in Power Query

[krrishkrsna]

hi

i have watched video of 1349 Excel Magic Trick 1349_ Power Query with Input Variables from Excel Sheet to Extract Records.mp4

please let me know how to do it on multiple criteria like Aspen, Quad and USA, Africa, America

thanks

 

[MarcelBeug]

Extend the Product and Region tables in Excel.

Adjust the queries ProductCriteria and RegionCriteria: just remove the last line of each.

Adjust the #"Filtered Rows" step in query FinalReport to:

#"Filtered Rows" = Table.SelectRows(#"Changed Type", each (List.Contains(ProductCriteria[Product],[Product])) and (List.Contains(RegionCriteria[Region],[Region]))),

 

[krrishkrsna]

thanks for the reply

updated your code in fSales Advanced editor but facing the below Error

Expression.Error: We cannot apply field access to the type Text.
Details:
Value=Carlota
Key=Product


in fSales Advanced Query Editor

let
PrdCri01 = ProductCriteria,
RegCri01 = RegionCriteria,
Source = Excel.CurrentWorkbook(){[Name="fSales"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Product", type text}, {"Region", type text}, {"Units", Int64.Type}, {"Category", type text}, {"Discount", type number}, {"Price", type number}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each (List.Contains(ProductCriteria[Product],[Product])) and (List.Contains(RegionCriteria[Region],[Region])))
in
#"Filtered Rows"



in RegionCriteria Advanced Query Editor
let
Source = Excel.CurrentWorkbook(){[Name="RegionCriteria"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Region", type text}}),
RegCri01 = Record.Field(Source{0},"Region")
in
RegCri01





in ProductCriteria Advanced Query Editor
let
Source = Excel.CurrentWorkbook(){[Name="ProductCriteria"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}}),
PrdCri01 = Record.Field(Source{0},"Product")
in
PrdCri01

using the file EMT1349Start.xlsm

selected Carlota and Sunshine in Product in excel sheet
selected Europe in Region in excel sheet

can't we use Source{0..2} in RegionCriteria and ProductCriteria advanced editor

today i updated PowerQuery with April2017 updates.

plz HELP

 

[krrishkrsna]

please Ignore the above Reply.

THANKS sir for you Answer

one silly question to you
is Power Query a replacement to Excel VBa Macros. i mean can we do all work in Power Query instead of writing Macros

is it possible to call VBa procedure in Power Query

 

[MarcelBeug]

Power Query is primarily meant to get and transform data from many sources into a data model (alternatively: to Excel tables).
From there, data can be further enhanced using DAX (Power Pivot) and used for reporting.
With Power BI (separate product, which includes both Power Query and DAX) you can create and publish all kinds of visualizations (dashboards).

Power Query is not a replacement for VBA macros. Basically the only output from Power Query is data (in a data model and/or in tables), while VBA has far more functionality.
It is not possible to call a VBA procedure from Power Query.
The other way around it is possible to have a query refreshed using VBA (or do other things with queries from VBA).

 

[krrishkrsna]

thanks for information