Reply
Topic Options
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Changing YYYYMM to a valid date format
09-05-2022
05:42 AM
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
Solved! Go to Solution.
1 ACCEPTED SOLUTION
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
09-05-2022
08:54 AM

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
Now accepting Kudos! If my post helped you, why not give it a thumbs-up?
Proud to be a Datanaut!
2 REPLIES 2
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
09-05-2022
10:37 AM

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"]))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
09-05-2022
08:54 AM

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
Now accepting Kudos! If my post helped you, why not give it a thumbs-up?
Proud to be a Datanaut!

Helpful resources
Top Solution Authors (Last Month)
User | Count |
---|---|
7 | |
5 | |
5 | |
5 | |
4 |
Top Kudoed Authors (Last Month)
User | Count |
---|---|
11 | |
11 | |
9 | |
7 | |
6 |