DirectQuery orders by day from DateTime Field

[Miss]

I'm trying to produce a report to show the number of orders placed by day, using directQuery so we have the report as live as possible

The Data I have access to for a report gives the date and time an order was placed:

OrderID	CreatedAt
000000004	12/03/2020 09:46:26
000000005	12/03/2020 15:04:05
000000006	13/03/2020 15:06:29
000000007	13/03/2020 15:17:56
000000008	13/03/2020 15:42:51
000000009	16/03/2020 12:34:10
000000010	16/03/2020 12:51:34

As this is a direct query, it will not allow me to convert this to a date field. I've also tried to split the column but this is not supported either.

When I create a visual to show the number of orders per day, it displays each order individually due to the unique timestamps, rather than grouping by day

Has anyone got any workarounds that would help to convert the createdAt field to a date only field? Or any other clever but simple ideas?!

Im using a custom connector and I have no ability to change to original data source to meet my requirements!

Any help would be most appreciated!

Thanks

[v-shex-msft]

HI @Miss,

What type of data source are you test? Did this support SQL statements or other query string options? I'd like to suggest you write a query with sort functions instead changes them on power bi side. 

Refresh data from an on-premises SQL Server database 

Regards,

Xiaoxin Sheng

[Miss]

I'm using a CData Connector to connect to magento. I cant use a custom sql statement with this connector on direct query unfortunately.

Is there any other way to amend the sql where I could change it after I've created the connection?

[v-shex-msft · Accepted solution]

HI @Miss,

Maybe you can consider using odbc driver or web connection with rest API to instead.

ODBC Driver:

Connecting to Magento from Microsoft Excel using ODBC Driver for Magento  

ODBC Data Sources, The SqlCapabilities Option And Power Query/Power BI Data Refresh Performance 

Web connector:

Magento - Getting Started with Web APIs 

Get Data from Twitter API with Power Query
Power BI – Connect to your secure API

Regards,

Xiaoxin Sheng

[camargos88]

Hi @Miss ,

How are you submitting this query ?

Can't you format your date column on select ?

Ricardo