Forum Discussion

Sage_user's avatar
Sage_user
New Member
3 years ago

ODBC clauses as a separate row

Hi all,

 

Using Sage to create an ODBC connection with Power Query, however I want line one to show DATEFROM (XX/XX/XX) and then DATETO (XX/XX/XX), then my data in line three to be updated using these dates.

 

I've tried to add WHERE clause and GETDATE but nothing seems to work! The data is linked to a nominal table in Sage, like below:-

 

let
Source = Odbc.DataSource("dsn=SageLine50v28", [HierarchicalNavigation=true]),
AUDIT_JOURNAL_Table = Source{[Name="AUDIT_JOURNAL",Kind="Table"]}[Data],
#"Removed Columns" = Table.RemoveColumns(AUDIT_JOURNAL_Table,{"BANK_CODE", "BANK_FLAG", "DATE_ENTERED", "DATE_FLAG", "DELETED_FLAG", "DEPT_NAME", "DEPT_NUMBER", "DISPUTED", "DISPUTE_CODE", "EXTRA_REF", "FOREIGN_AMOUNT", "FUND_ID", "HEADER_NUMBER", "INV_REF", "OSS_COUNTRY_OF_VAT", "OSS_REPORTING_TYPE", "OSS_REPORTING_TYPE_NAME", "PAID_FLAG", "PAID_STATUS", "RECORD_CREATE_DATE", "RECORD_DELETED", "RECORD_MODIFY_DATE", "RTD_FLAG", "SPLIT_NUMBER", "STMT_TEXT", "VAT_FLAG", "VAT_FLAG_CODE", "VAT_LEDGER_RETURN_ID", "VAT_RECONCILED_DATE", "NOMINAL_CODE"})
in
#"Removed Columns"

Need Help
Tutorial Requests

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Sage_user 

    You can consider to filter the data.e g 

    Table.SelectRows(#"Changed Type", each [Column1] >= #date(2021, 5, 1) and [Column2] <= #date(2023, 9, 1))

    If the following above cannot meet your requirement, can you provide some sample picture or output you want? 

     

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

    • Sage_user's avatar
      Sage_user
      New Member
      2 years ago

      Hi Yolo Zhu and thanks for helping, I really appreciate it!

       

      Do I enter this in advanced editor under 'let'? Thus it being the below (gives an error). Sample picture of what i want is below also..

       

       

       

      let
      Source = Odbc.DataSource("dsn=SageLine50v28", [HierarchicalNavigation=true]),
      AUDIT_JOURNAL_Table = Source{[Name="AUDIT_JOURNAL",Kind="Table"]}[Data],
      #"Removed Columns" = Table.RemoveColumns(AUDIT_JOURNAL_Table,{"DATE_ENTERED", "DATE_FLAG", "DELETED_FLAG", "DEPT_NAME", "DEPT_NUMBER", "DISPUTED", "DISPUTE_CODE", "FUND_ID", "OSS_COUNTRY_OF_VAT", "OSS_REPORTING_TYPE", "OSS_REPORTING_TYPE_NAME", "PAID_FLAG", "PAID_STATUS", "RECORD_CREATE_DATE", "RECORD_DELETED", "RTD_FLAG", "SPLIT_NUMBER", "STMT_TEXT", "VAT_FLAG", "VAT_FLAG_CODE", "VAT_LEDGER_RETURN_ID", "VAT_RECONCILED_DATE"}),
      #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"ACCOUNT_REF", "DETAILS", "AMOUNT", "FOREIGN_AMOUNT", "EXTRA_REF", "BANK_CODE", "BANK_FLAG", "DATE", "HEADER_NUMBER", "INV_REF", "NOMINAL_CODE", "RECORD_MODIFY_DATE", "TAX_CODE", "TRAN_NUMBER", "TYPE", "USER_NAME"}),
      #"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"BANK_CODE", "BANK_FLAG", "HEADER_NUMBER"}),
      #"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns1",{"ACCOUNT_REF", "NOMINAL_CODE", "DETAILS", "TYPE", "AMOUNT", "FOREIGN_AMOUNT", "EXTRA_REF", "DATE", "INV_REF", "RECORD_MODIFY_DATE", "TAX_CODE", "TRAN_NUMBER", "USER_NAME"}),
      #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns1",{{"DATE", type date}})

      Table.SelectRows(#"Changed Type", each [Column1] >= #date(2021, 5, 1) and [Column2] <= #date(2023, 9, 1))
      in
      #"Changed Type"