Blog Post

Azure Data Explorer Blog
2 MIN READ

How Date and Time values from ADX are reflected in Power BI

DanyHoter's avatar
DanyHoter
Icon for Microsoft rankMicrosoft
Aug 27, 2023

 

 

Summary

Data in ADX (aka Kusto aka RTA in Fabric) almost always has columns that contain datetime values like 2023-08-01 16:45 and sometimes timespan values like 2 hours or 36 minutes.

In this article I’ll describe how these values are represented in ADX in Power Query and in Power BI.

Notice that I don’t just say Power BI because timespan values have different types in Power Query and in Power BI.

Dates and times in ADX

 

ADX have to relevant data types : datetime and timespan.

In the attached pbix file, there are two basic columns, one is a datetime and one is a timespan.

Datetime values are assumed to be in UTC. They can be converted  to local time using the datetime_utc_to_local function.

 

 

Here is the query that creates the data in KQL: (You can click on the web Hyperlink to run the query)

Execute in [Web] [Desktop] [cluster('help.kusto.windows.net').database('ContosoSales')]

datatable(Date_and_Time:datetime, Span:timespan ,Spantext:string) [

    datetime(2023-09-01), 2m,"2m",

    datetime(2023-09-01 06:00), 2h,"2h",

    datetime(2023-09-01 06:25:33),2d,"2d",

    datetime(2023-09-01 23:55:12.1234), 30s, "30s",

    datetime(2023-09-30),251ms,"251ms"

]

| extend Time_In_New_York=datetime_utc_to_local(Date_and_Time,"America/New_York")



Table0

Date_and_Time

Span

Spantext

Time_In_New_York

2023-09-01T00:00:00Z

00:02:00

2m

2023-08-31T20:00:00Z

2023-09-01T06:00:00Z

02:00:00

2h

2023-09-01T02:00:00Z

2023-09-01T06:25:33Z

2.00:00:00

2d

2023-09-01T02:25:33Z

2023-09-01T23:55:12.1234Z

00:00:30

30s

2023-09-01T19:55:12.1234Z

2023-09-30T00:00:00Z

00:00:00.2510000

251ms

2023-09-29T20:00:00Z

 

Dates and time types in Power Query

 

The datetime columns in ADX are typed as Date/Time/Zone.

Changing the type to datetime will not change the value because the datetime is assumed to be in UTC .

 

 

The timespan column is typed as duration.

 

Date and Time types in the PBI data model

 

Power BI data types are described here.

The paragraph about Date/time types explains how Power Query types are mapped to the data model.

Some types are converted when moved from Power Query to the data model.

Date/Time/Timezone is converted to Date/Time and Duration is converted to decimal.

The behavior of timespan->duration->decimal needs some explanation.

A timespan of 12h is shown in PQ as 0.12:0:0 and in the model as 0.5.

For those of you with an Excel background, it will make perfect sense.

0.5 is half a day, the decimal is expressed as the decimal parts of a day.

For someone coming from KQL background it looks odd.

The timespan column can be converted in the query to seconds by multiplying it by 24*60*60 which is the number of seconds in 24 hours.

 

 

 

 

Published Aug 27, 2023
Version 1.0
ADX
date
Fabric
kusto
PBI
power bi
RTA
Time
  • ItsBhatti's avatar
    ItsBhatti
    Brass Contributor
    Aug 27, 2023

    In Power BI, you can work with Date and Time values from ADX (Azure Data Explorer) by following these steps:

    1. Import Data: Connect Power BI to your ADX data source by selecting the appropriate connector. This might involve setting up the necessary connection details like server name, database, and credentials.

    2. Query Editor: Once connected, you'll enter the Query Editor where you can transform and shape your data before loading it into Power BI.

    3. Data Types: ADX usually provides Date and Time values in a specific format. Power BI will automatically detect these as text values. You need to change their data type to "Date/Time" for proper manipulation.

      • Select the column containing Date or Time values.
      • In the "Modeling" tab, find the "Data Type" dropdown and select "Date/Time."

    4. Date Hierarchy: If your Date column has a hierarchical structure (Year, Quarter, Month, etc.), you can create a Date Hierarchy to easily navigate time-based data.

      • Right-click on the Date column.
      • Choose "New Hierarchy" and then define the hierarchy levels.

    5. Time Intelligence Functions: Power BI has built-in time intelligence functions that allow you to perform calculations like year-to-date, quarter-to-date, etc.

      • Create new measures by going to the "Modeling" tab > "New Measure".
      • You can use functions like TOTALYTD, TOTALQTD, TOTALMTD, etc., to calculate time-based aggregates.

    6. Visualizations: Once your data is ready, you can create visualizations to represent Date and Time data.

      • Drag and drop a Date field to the "Axis" or "Legend" area of a visualization.
      • Depending on the visualization type, you can represent trends over time.

    7. Filters: You can use date filters to control the time range displayed in your visuals.

      • Add a date filter to a visualization by selecting the Date field, then choosing the "Filter" icon in the Visualizations pane.

    8. Custom Calculations: If you need to perform custom calculations involving Date and Time fields, you can use DAX (Data Analysis Expressions) formulas.

      • Create a new measure and use DAX functions to manipulate the date/time data.

    Remember that the specifics might vary based on your data structure, the version of Power BI you're using, and the nature of your analysis. If you encounter any specific issues or need help with particular calculations, feel free to provide more details for further assistance.