Importing Date Field using Excel vs Import from Azure SQL

[mobioki]

Our PowerBI setup links directly to a SQL DB in Aure.  When importing the data, the date field (which is formatted as SMALLDATETIME) imports into 1 field. However, when we take the same data and import it into PowerBI from an excel file where the date field (startdate) is formated as dd-mmm PowerBI automatically creates two new fields startdate month and start date year.

How do I get the SQL DB to do the same when that date field is imported?

[Greg_Deckler]

@mobioki - There's some sort of magic that goes on where sometimes this happens and sometimes not, I haven't been able to figuire out the pattern. That being said, you can always create these yourself:

StartDate Month = MONTH([startdate])

StartDate Year = YEAR([startdate])

M code, you would use Date.Month and Date.Year

[mobioki]

Thanks. I do that but then Month and Year are Integers. So that when I look at data from 2015 (Nov, Dec) and 2016 (Jan) in a single visualation Jan is shown under 1 and Nov and Dec are shown as 11 and 12.  This view does not keep the chronological order of the dates.

[greggyb]

You can simply concatenate the two fields together and cast to an integer data type, or a nice display field:

// Power Query
// Create an integer in the format YYYYMM

= [Year] * 100 + [Month]

// Create a nicely displayed text string: YYYY-MMM
= Date.ToText( #date( [Year], [Month], 1 ), "YYYY-MMM" )

If you want to use the second for a good display field, you'll have to create the first as a sort column and sort the display field by the numeric one so that they are in chronological order.