Forum Discussion

Iaine's avatar
Iaine
Frequent Visitor
3 years ago
Solved

Changing YYYYMM to a valid date format

I wonder if anyone has figured out how to take 'YYYYMM' as a number field to turn it into a valid time period in power query. I have manged it in Power Bi Desktop, but as i need to do some things on it in power query it does not show.

 

Any help would be awesome 

 

Iain 

Need Help
  • BA_Pete's avatar
    BA_Pete
    3 years ago

    Hi Iaine ,

     

    You could create a new custom column, something like this:

    #date(
    Number.From(Text.Start(Text.From([dateKeyField]), 4)),
    Number.From(Text.End(Text.From([dateKeyField]), 2)),
    01
)

     

    This gives me the following output:

     

    Pete

2 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    3 years ago

    You can also use math functions to isolate the year and month components.

     

    #date(
    Number.IntegerDivide([Column1], 100),
    Number.Mod([Column1],100),
    1
)

     

     

    Another option is to use Date.FromText with an explicit Format specification:

     

    Date.FromText(Text.From([Column1]), [Format="yyyyMM"]))

     

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

    Hi Iaine ,

     

    You could create a new custom column, something like this:

    #date(
    Number.From(Text.Start(Text.From([dateKeyField]), 4)),
    Number.From(Text.End(Text.From([dateKeyField]), 2)),
    01
)

     

    This gives me the following output:

     

    Pete