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.
And we have anther table call DATE_DIM table, which have the filld called Date_Key which is number too
So we can join REQUESTED_DATE_KEY=Date_Key in order to get the calenday day, then in my sales table, I can filter only for today, becasue I have milloin rows in sales table, so I only want today.
But I don't know how to do this in powerbi in query editor mode, I try to merge those tow tables together, but it taks forever to do.
Do anyone one knows what is the right way to do this?
8 Replies
Hey NoobAnalyst01
Since you probably want to keep your full Sales table for other use I would do the following:
- Duplicate your Sales table
- Filter the duplicated table down to Today
- To do this, Use the date filter setting on the dropdown of the field
- Set it to an arbitrary date
- Open up the advanced editor and replace the date with DateTime.LocalNow()
- Then perform your merge
Hope this helps,
Parker
Hello,
Parker, what do you mean by:
- To do this, Use the date filter setting on the dropdown of the field
- Set it to an arbitrary dat
Sorry, i am new to power bi, i didn;t get your method.
I realized that your Date column you're trying to filter on is in number format. You will need to either change it to a date or create a date column using the Query Editor. You can easily acheive this by clicking Column from Examples and then typing the date format of one of your rows. For example, if the date key is 06252018 you can type 06/25/2018 and it will create a new column with proper dates. When you have this new column, use a date filter like such:
Click on Equals and set the date equal to literally any date you want. Then follow the steps outlined above!
What method are you using to bring in your Sales table? SQL?
imported. but the sales table didn't has delivery_date, i have a filed called date key, you have to join this date key with the anohter table called date dim, in the date dime, each date key refers calenedar day!
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.
