Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
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 th...
Need Help
Tutorial Requests
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi Anonymous 

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.

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    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"