Forum Discussion
struggle
Helper II
Helper IICreating custom columns keeps returning a structured Table column
For example, since Power Query isn't able to autoconvert the column Date, which is in yyyymmdd format, into date format, I've been trying to convert it using different custom column calculations, but...
If you are using the UI to add a column you do not need to write "Table.AddColumn". You would only need to write everything you have after the 'each'
MasonMA
Super User
Super User
Hi, you may need to change your data in 'Date' column to a 'Text' data type before your Add Column step.
I used some random data and gave it a try with below M and it works
= Table.AddColumn(#"Changed Type", "Custom", each #date(
Number.FromText(Text.Start([Date], 4)),
Number.FromText(Text.Middle([Date], 4, 2)),
Number.FromText(Text.End([Date], 2))
))
Hope it helps:)
struggleHelper II
Helper III do get the correct dates, but they're still behind Table links in the resulting column.
- MasonMA
Now I'm not sure i understand your question.
Could you paste all your M code here?
- struggle
I've tried both this
Table.AddColumn(Access_Table, "Proper Date", each
let
DateNumber = [Date],
Year = Number.IntegerDivide(DateNumber, 10000),
Month = Number.IntegerDivide(Number.Mod(DateNumber, 10000), 100),
Day = Number.Mod(DateNumber, 100)
in
#date(Year, Month, Day)
)and this
= Table.AddColumn(Access_Table, "Proper Date", each
Date.FromText(Text.From([Date]), [Format="yyyymmdd"])
)And the resulting column populates Table links instead of the calculated values:
For the second calculation, I get this error when I click through the Table links to see the values:
Parameter.Error: We couldn't use the specified value as a date format because it includes a time component.
Details:
Format=yyyymmdd
jgeddesWhat does
Table.AddColumn(#"Added Column", "Proper Date", each Date.From([Date]), type date)return?