Control the data imported by another table

[NoobAnalyst01]

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?

[drewlewis15]

What method are you using to bring in your Sales table?  SQL?

[NoobAnalyst01]

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!

[drewlewis15]

The date key in your table appears to simply be the date in a YYYYMMDD format.  If so, then follow these steps:

1. Start a blank query: New Source > Blank Query
2. Enter the following statement into the formula bar:
   1. Number.FromText(Date.ToText(DateTime.Date(DateTime.LocalNow()), "YYYYMMDD"))
3. Name that query "Today"
4. 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.
5. 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))
6. 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.

[NoobAnalyst01]

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

[Anonymous]

Hey @NoobAnalyst01

Since you probably want to keep your full Sales table for other use I would do the following:

1. Duplicate your Sales table
2. Filter the duplicated table down to Today
   1. To do this, Use the date filter setting on the dropdown of the field
   2. Set it to an arbitrary date
   3. Open up the advanced editor and replace the date with DateTime.LocalNow()
3. Then perform your merge

Hope this helps,

Parker

[NoobAnalyst01]

Hello,

Parker, what do you mean by:

1. To do this, Use the date filter setting on the dropdown of the field
2. Set it to an arbitrary dat

Sorry, i am new to power bi, i didn;t get your method.

[Anonymous]

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!

[NoobAnalyst01]

I changed the datekey, it give a error , since it is not date, the datekey joined with another datekey in Date_Dim table, then you can get teh day.

you mean i should type the date fromat i want to my datekey, i did, it gives me: