how to change data type as TEXT without affecting Date Format in Excel Power Query.

[AJ_121]

Dear,
I am making Formula of "FROM DATE" & "TO DATE" where Value is Type is Date And Format is dd-mm-yyyy.

When i make it a symbol in Power Query it shows date with time and when i change Data Type as Date then it comes in my required format(dd-mm-yyyy)-Ref attached image1. But when i change Data Type as Text then Date Format Change automatically in m/d/yyyy- Ref attached image2.

I Need the Data Type as TEXT and Date Format in dd-mm-yyyy.

 

[mole999]

how about =TEXT(cell,"dd-mm-yyyy")

 

[AJ_121]

how about =TEXT(cell,"dd-mm-yyyy")

Dear
I am Sorry.
I didn't get you.

Where i have to put this formula in Power Query or in Sheet Cell, which you have mentioned??

If I have to put it on Sheet then there is a formula which is ( Current Date-Number ), therefore i cant change anything on Sheet. Only have to change in Power Query.

Please Help.

 

[sandy666]

try Text.From([Date], "en-GB")
eg. = Table.AddColumn(#"Changed Type", "Custom", each Text.From([Date], "en-GB"))

 

[AJ_121]

Dear,
I am new in Power Query therefore i am unable to get you.
What i need i am clarifying below :
I need the data from a website where i have to put the date format DD-MM-YYYY. Therefore i am making a formula where i will simply type the date in excel and i will get the data via power query. Therefore i am doing : Selecting data - Data-From Table/Range Now its showing like Image1 Then i am changing Data Type into Date and is showing like Image2 But i need to change the Data type into Text and when i change it into Text then its showing like image3. BUT as you know that I need the Date Format in DD-MM-YYYY without time.


Kindly Provide the full solution.

Thank you.

 

[sandy666]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"FromDate", type date}}),
    TextDate = Table.AddColumn(Type, "TextDate", each Text.From([FromDate], "en-GB"))
in
    TextDate

that's all what I can do with information you provided

 

[sandy666]

I suggest to read more here:

• What is PowerQuery
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference

 

[sandy666]

or like this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TypeTextLocale = Table.TransformColumnTypes(Table.TransformColumnTypes(Source,{{"FromDate", type date}}), {{"FromDate", type text}}, "en-GB")
in
    TypeTextLocale

 

[AJ_121]

Thanks a lot Sandy. Its Working. I changed "en-GB" into "en-IN" and i got my desired Date Format.

I really appreciate your help.

 

[sandy666]

You are welcome