Issue with date field in direct query mode

[Anonymous]

Hi ,

I have 2 tables-Table A and Table B that are joined by fileid column. I am displaying the data in Table A in a table chart. In another table visual I am displaying the data from Table B. My aim is to select any filename from Table B and see the relevant filenames or records in Table A since I created a relationship for the 2 tables based in fileid column.
Issue:  If I put only  filename and fileid in Table B , then it is filtering the rows of Table A properly. If I add the process_date column to Table B, it is not filtering Table A.

Note:  I am using direct query mode to get the data since data needs to be refreshed every 15 mins. In import mode it is working fine. Issue is only when using in direct query mode. Only difference I could see between import and direct query is, in import the date filed as recognised as date but in direct query its not.

Any help is appreciated. Thanks in advance!!

Regards,

Priyanga

[Anonymous]

HI @Anonymous 

In Import query mode all the fields are imported as text, what ever it may be.

On the power query editor or on the 'Column Tools' option of the desktop mode once you chage the type to date yor filter will start working. I have the same scenario and it works after changing the data type.

[Anonymous]

@Pranit, Query editor has detected it as a datetime field. Its not in text

[Anonymous · Accepted solution]

I wrote a sql query for Table B, and converted the date field to varchar.. Strangely it worked after converting the date field to text.

Regards,

Priyanga

[Greg_Deckler]

@Anonymous - That's odd, are you sure that when you add the date column that it is preserving the relationship between the two tables? If you field is not a date in Direct Query mode, you will need to fix that on the database side of things.

[Anonymous]

@Greg,It is already in datetime datatype in SQL DB