Forum Discussion

Indika156's avatar
Indika156
Frequent Visitor
8 years ago
Solved

Advanced Editor

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 

Data Modeling
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    8 years ago

    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 

3 Replies

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community Support
    8 years ago

    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's avatar
      Indika156
      Frequent Visitor
      8 years ago

      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's avatar
        v-qiuyu-msft
        Community Support
        8 years ago

        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