Forum Discussion
No data in streaming dataset from Azure Stream Analytics to Power BI
Hi ttrail,
I'd like to suggest you use the date format function to format the value. In your query, you merge the value and try to convert it to datetime, it may cause some compatibility issue, you can try to use below date format function to format the date.
Function:
DATETIMEFROMPARTS (year, month, day, hour, minute, seconds, milliseconds)
Example:
SELECT EntryTime, DATETIMEFROMPARTS(2014,9,10,12,DATEPART(minute,EntryTime)+10,00,00)
AS ExitTime
FROM Input TIMESTAMP BY EntryTime
WHERE Toll > 5
Referece link:
DATETIMEFROMPARTS (Azure Stream Analytics)
Regards,
Xiaoxin Sheng
Thanks for the suggestion, Anonymous. I changed the query to replace the CONCAT which at least made my data accessible (although not real time -- see details below). The revised, ridiculously complex query is now:
SELECT
readingdate,
DATETIMEFROMPARTS(DATEPART(year, readingdate), DATEPART(month, readingdate), DATEPART(day, readingdate),
DATEPART(hour, readingtime), DATEPART(minute, readingtime), DATEPART(second, readingtime), 0) AS datetime,
avg(soilmoisture*.393701) as soilmoisture,
name
INTO
[soilsensorpowerbi]
FROM
[collineariothub]
GROUP BY TumblingWindow(minute, 3), readingdate, readingtime, name
The weird thing is the difference though. Now, rather than showing up in Power BI as a "Streaming dataset", it shows up under Datasets as a regular dataset. This means the data in my reports does not automatically refresh real-time and I cannot add a tile to a dashboard since it is not recognized this as a streaming dataset. Ugh! Guess I need to create another separate post.