Advanced Editor

[Indika156]

Hi 

I need to load the specific columns and rows from the table (ODBC Progress db) , I dont want all the columns and rows to load ,

I know how to get the columns but dont know how to include the filtering coding in the same syntax 

Filtering after all columns loaded is not the solution , it should only takes the filtered rows

let
Source = Odbc.DataSource("dsn=stix", [HierarchicalNavigation=true]),
STIX_Database = Source{[Name="STIX",Kind="Database"]}[Data],
PUB_Schema = STIX_Database{[Name="PUB",Kind="Schema"]}[Data],
trndet_Table = PUB_Schema{[Name="trndet",Kind="Table"]}[Data],
Sales = Table.SelectColumns(trndet_Table,{"id_branch", "id_maj_grp_prod","cost_line","sell_line","date_invoiced"}),
Sales=Table.SelectRows(#"sales", each [date_invoiced] > #date(2018, 5, 1))
in
#"Sales"

The above is the syntax and bold is the row filtering , how can I joing thees two , please help me 

[v-qiuyu-msft]

Hi @Indika156,

You can modify the Power Query below: 

let
Source = Odbc.DataSource("dsn=stix", [HierarchicalNavigation=true]),
STIX_Database = Source{[Name="STIX",Kind="Database"]}[Data],
PUB_Schema = STIX_Database{[Name="PUB",Kind="Schema"]}[Data],
trndet_Table = PUB_Schema{[Name="trndet",Kind="Table"]}[Data],
Sales = Table.SelectColumns(trndet_Table,{"id_branch", "id_maj_grp_prod","cost_line","sell_line","date_invoiced"}),
Sales2=Table.SelectRows(Sales, each [date_invoiced] > #date(2018, 5, 1))
in
Sales2

Best Regards,
Qiuyun Yu 

[Indika156]

Hi

Thanks , here also it create the Sales table first with all the rows and then it start filtering , so it takes time ,I want to combine the select columns and rows coding in one statement

[v-qiuyu-msft · Accepted solution]

Hi @Indika156,

Please use below query: 

let
Source = Odbc.DataSource("dsn=stix", [HierarchicalNavigation=true]),
STIX_Database = Source{[Name="STIX",Kind="Database"]}[Data],
PUB_Schema = STIX_Database{[Name="PUB",Kind="Schema"]}[Data],
trndet_Table = PUB_Schema{[Name="trndet",Kind="Table"]}[Data],
Sales2=Table.SelectRows(Table.SelectColumns(trndet_Table,{"id_branch", "id_maj_grp_prod","cost_line","sell_line","date_invoiced"}), each [date_invoiced] > #date(2018, 5, 1))
in
Sales2

Best Regards,
Qiuyun Yu