Forum Discussion

mobioki's avatar
mobioki
New Member
10 years ago

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 impo...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
10 years ago

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's avatar
mobioki
New Member
10 years ago

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's avatar
    greggyb
    Icon for Resident Rockstar rankResident Rockstar
    10 years ago

    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.