Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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"
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.
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"
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
41 | |
29 | |
23 | |
21 | |
17 |