Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a date in the date table
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?
Solved! Go to Solution.
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")})
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.
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")})
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.
@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.
Proud to be a Super User!
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
This time Im getting Expression.Error: A cyclic reference was encountered during evaluation.
@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.
Proud to be a Super User!
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 worked
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.
Proud to be a 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")
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |