Select all Date Columns to convert

[GreenBoy]

Hi
I have a large data set which has over 30 date type columns spread amongst 200 columns total.

Some are in the format I need and others are Date Time etc. I want / need to convert the type into just Short Date.

Is there a way to do this to the freshly imported data without manually searching for all the date fields (they dont all helpfully contain the word 'date' )

So basically -

1. Import data
2. Select all fields/ columns that are of a date type
3. convert them to Short Date.

Thanks

 

[horseyride]

Try this

    Lookat = Table.Schema(PreviousStep),
    // Find all columns where current type contains the word "Date"
    FilterType = Table.SelectRows(Lookat, each Text.Contains([TypeName], "Date")),
    ColumnList= Table.SelectColumns(FilterType,{"Name"}),
    // Convert all those selected columns to type date
    AddType = Table.AddColumn(ColumnList, "Type", each Expression.Evaluate("type date")),
    FieldValues = Table.AddColumn(AddType, "Custom", each Record.FieldValues(_)),
    RemovedColumns = Table.RemoveColumns(FieldValues,{"Name", "Type"}),
    ColumnSpecs = RemovedColumns[Custom],
    Doit = Table.TransformColumnTypes(PreviousStep,ColumnSpecs)

 

[GreenBoy]

Wow Horseyride - you did it again

Im struggling picking this stuff up - can you recommend a good source of information. For example i cannot find anything to explain the 'Lookat' function you have used...

Any pointers gratefully received.

Thanks

 

[horseyride]

I'm more of an as-needed self-taught hack myself whenever an interesting problem arises. However, the online reference for M is very valuable. See https://msdn.microsoft.com/en-us/query-bi/m/table-schema

 

[GreenBoy]

Thanks Horsey - have been to this site before, but always seems to leave me asking more questions than when i arrived! :confused2: