ODBC clauses as a separate row

[Sage_user]

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"

[Anonymous]

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]

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"