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 fi...
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 

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