• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Date Format in power query

Kamo

Member
Hello,

In the attached file, I would like to format the cells containing dates.
How can I get this automatically in Power query?
Thank you in advance for your help

Sincerely
 

Attachments

AlanSidman

Well-Known Member
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"2", type date}, {"5", type date}, {"8", type date}})
 

Kamo

Member
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"2", type date}, {"5", type date}, {"8", type date}})
Hi AlanSidman,
Thank you for your reply,
It works, but is it possible to make the code dynamic because my list can evolve over several hundred rows

Thank you in advance
 

p45cal

Well-Known Member
In Table1 (2) query at cell F13 of the attached has a simple query. There are 3 steps:
ColmHdrs which gets a list of column headers
Types which produces a short list of the 3 types, repeating for as many times there are headers/3 (I haven't tried if it doesn't divide exactly by 3!)
zipped which brings them both together as a list of lists to use in the final step, Changed Type1.

I don't understand the reasoning behind the step Integer-Divided Column in the Table1 query; it puts all zeroes in the Modulo column.
my list can evolve over several hundred rows
You're going to end up with a very wide table!
 

Attachments

Kamo

Member
I don't understand the reasoning behind the step Integer-Divided Column in the Table1 query; it puts all zeroes in the Modulo column.
You're going to end up with a very wide table!
Hello p45cal,
Great thanks a lot, that's what I wanted to get.
To answer your question, I hadn't thought of the "Table.Transpose" function. :)
It's actually more direct than with the index steps

Otherwise for your comment "You're going to end up with a very wide table! ",
In reality this is a fictitious exercise, I simply wanted to test on several hundred rows

Thanks again

Sincerely
 
Last edited:
Top