cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User I
Super User I

Best way to create a date Key in M e.g. 01012020

I have a date in the date table

 

Dates.JPG

And I want a date key of 01012020 for example

 

however previously I have to Duplicate the date three times. Change them to day month and year

Then add a 0 to the beginning because 01 gets changed to 1

Then split out the column Right 2 to remove any 0s not required

All the time constanly changing the number to text

 

Its really problematic and Im thinking surely there must be an easier way of doing things in Power Query Editor?

 

 

1 ACCEPTED SOLUTION
Microsoft
Microsoft

Hi, @DebbieE 

 

It’s my pleasure to answer for you.

According to your description,I think you can custom a column in PQ.

First you need to change the type to date.

Like this:

Text.Combine({Date.ToText([Date], "dd"),Date.ToText([Date], "MM"), Date.ToText([Date], "yyyy")})

6.png5.png

If it doesn’t solve your problem, please feel free to ask me.

 

Best Regards

Janey Guo

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

11 REPLIES 11
Microsoft
Microsoft

Hi, @DebbieE 

 

It’s my pleasure to answer for you.

According to your description,I think you can custom a column in PQ.

First you need to change the type to date.

Like this:

Text.Combine({Date.ToText([Date], "dd"),Date.ToText([Date], "MM"), Date.ToText([Date], "yyyy")})

6.png5.png

If it doesn’t solve your problem, please feel free to ask me.

 

Best Regards

Janey Guo

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Super User II
Super User II

@DebbieE  You can change the format of the date in the power query as well. Pl. add a custom column like below

Text.Combine({Date.ToText([Date], "MM"), "01", Date.ToText([Date], "yyyy")})

 

Like below I have added a custom column from the date field and the output is what you wish to have.

 

negi007_0-1604312671569.png

 

Ive had to change this because I want dd mm and year and not 01 as the day

Text.Combine('[Date],"DD"),DateDate.ToText([Date],"MM",DateDate.ToText([Date],"yyyy")')

All Im getting is a custom column error

CustomColumnerror.JPG

@DebbieE 

 

use this

 

= Table.AddColumn(Source, "Custom", each Text.Combine({Date.ToText([Date], "dd"), Date.ToText([Date], "MM"), Date.ToText([Date], "yyyy")}), type text)

This time Im getting Expression.Error: A cyclic reference was encountered during evaluation.

 

CyclicColumn.JPG

@DebbieE  Do one thing, on powerquery window

 

1. Select your date column from "Add Column" menu select "Column from Examples"  -> from selection

2. This will show you a custom column window

3. Now in the custom, please type the sample value against 3-4 values till it automatically fill all values in the new custom column

4. then click on the "OK button"

if you follow above steps, you will surely reach to the solution.

 

else you can share your data sample, in text format so that I can do the simiiar simultion at my end. 

That seems to work. But I already have date key created the difficult way. It seems to be just copying the date key so I would have to try this on a date dimension without the key already created

yep its just copied my date key

= [DateKey]

And Ive tried it on just a table of continuous dates and it hasnt workedAddColumnsFromExamplesDidntwork.JPG

Hi @DebbieE 

Here's a query that does as you wish

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTISsxTMDRQitWJVjJC5hgjc0yQOabIHDNkjjkyxwKZY4nMMTSA82IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "DateKey", each Date.ToText([Date], "ddMMyyyy"))
in
    #"Added Custom"

 

and the PBIX file containing this query.

I've only entered 10 dates as a sample but you really only need this line to create the Datekey column

 

    #"Added Custom" = Table.AddColumn(PreviousStepName, "DateKey", each Date.ToText([Date], "ddMMyyyy"))

 

 

Regards

Phil 


If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

Super User IV
Super User IV

@DebbieE , Try a  new column like

date key = format([date], "DDMMYYYY")



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Im trying to do it in Power Query Editor.

Im assuming you mean 1. create a custom column

All I get when I create the custom column is Expression.Error: The name 'format' wasn't recognized. Make sure it's spelled correctly.

@DebbieE , Format was for DAX. In power Query, You have

Date.ToText([Date], "ddMMYYYY")



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors