Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DebbieE
Community Champion
Community Champion

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
v-janeyg-msft
Community Support
Community Support

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
v-janeyg-msft
Community Support
Community Support

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.

negi007
Community Champion
Community Champion

@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

 




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos



Proud to be a Super User!


Follow me on linkedin

DebbieE
Community Champion
Community Champion

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

negi007
Community Champion
Community Champion

@DebbieE 

 

use this

 

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




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos



Proud to be a Super User!


Follow me on linkedin

DebbieE
Community Champion
Community Champion

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

 

CyclicColumn.JPG

negi007
Community Champion
Community Champion

@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. 




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos



Proud to be a Super User!


Follow me on linkedin

DebbieE
Community Champion
Community Champion

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.



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


amitchandak
Super User
Super User

@DebbieE , Try a  new column like

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

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")

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.