Forum Discussion
Control the data imported by another table
Hello, I have a sales table, which include all inforamtion of my orders. But in this table, there is now datetime field, we have a column called REQUESTED_DATE_KEY wihch is number format. ...
The date key in your table appears to simply be the date in a YYYYMMDD format. If so, then follow these steps:
- Start a blank query: New Source > Blank Query
- Enter the following statement into the formula bar:
- Number.FromText(Date.ToText(DateTime.Date(DateTime.LocalNow()), "YYYYMMDD"))
- Name that query "Today"
- Go to your Sales table, and filter the REQUESTED_DATE_KEY field to "20180625". This will return today's data, but you want this to by dynamic so that you don't have to change it everyday... The next step will accomplish that.
- Go into the advanced editor and find the step that you just created to filter the REQUESTED_DATE_FIELD. Should look somehting like this: Table.SelectRows(#"Changed Type", each ([REQUESTED_DATE_KEY] = 20180625))
- Replace 20180625 with the word "Today". This will reference the value in your Today query setup in steps 1-2.
If you are importing your table via a SQL statement, then you can actually feed today's date into your SQL statement, so you don't have to import the millions of rows first... This would drastically improve your performance. I would just need to know what method you are using to import your table.
Hello,
I used improted method.
In out sales table, we don;t have the date, but we have datekey. we have a calendar table, which stores all the date and with some specila flag, for exmaple, if I want to pull yestreday's order, i use following SQL query
SELECT DATE_DIM.CALENDAR_DATE, SALES_HEADER_FACT.SALE_ID FROM DATE_DIM inner join SALES_HEADER_FACT on DATE_DIM.DATE_KEY=SALES_HEADER_FACT.REQUESTED_DATE_KEY WHERE DATE_DIM.YESTERDAY='Y'
As you can see use Date_Dim to control which days order we pull