Forum Discussion
Importing Date Field using Excel vs Import from Azure SQL
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?
3 Replies
- Greg_Deckler
Community Champion
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
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
Resident Rockstar
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.
