Creating custom columns keeps returning a structured Table column

[struggle]

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 every time I attempt this, I either get an error or a structured column filled with Table links. When I try to expand the resulting Proper Date column, I end up with extra rows. How do I fix this?

[jgeddes]

Power Query will convert that date column to a date format if you specify the culture it needs. 
https://learn.microsoft.com/en-ie/powerquery-m/date-from 
The Date.From and Date.FromText functions both have that ability.

[struggle]

My default is en-US, though - I don't see how specifying en-US would change that, or what culture I would have to specify othrewise. My main is that the output comes as links to a separate Table.

[jgeddes]

What code are you writing to add your column that results in a table?

[struggle]

See below: https://community.fabric.microsoft.com/t5/Power-Query/Creating-custom-columns-keeps-returning-a-stru...

[MasonMA]

@struggle 

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

[struggle]

I 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

[jgeddes]

What does 

Table.AddColumn(#"Added Column", "Proper Date", each Date.From([Date]), type date)

return?

[struggle]

[jgeddes]

Sorry, I wrote #"Added Column" instead of #"Added Custom". 
What is displayed if #"Added Custom" is used?

[struggle]

DataFormat.Error: We couldn't convert to Date.
Details:
20250302

[struggle]

Oh I think I know what it might be - I'm clicking the Custom Column function, which adds an extra 

Table.AddColumn(Access_Table, "Custom", each 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)
)) 
How do I add a custom column without incurring that code addition, or what do I omit?

[jgeddes · Accepted solution]

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' 

[struggle]

That worked with this code below. Thanks!

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)