Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
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
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
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |