Format date in M Script to show date only and not include T00:00:00

[Anonymous]

Hi Experts

How would you format the following M Script to only show date field as date only and not include T00:00:00 (time stamp) to the date field, which i believe what Power BI is doing some how to the date column.

M Script

let

    Source = CData.REST.DataSource("CDATA_CONNECTOR", null, [Query=null]),

    REST_Schema = Source{[Name="REST",Kind="Schema"]}[Data],

    Parts_v1.1_Table = REST_Schema{[Name="Parts_v1.1",Kind="Table"]}[Data],

    #"Filtered Rows" = Table.SelectRows(Parts_v1.1_Table, each [Fonto_effectiveDate] = Para_Date),

    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Fonto_documentId] = Para_Doc)

in

    #"Filtered Rows1"

[v-lionel-msft]

Hi @Anonymous ,

Couldn't you achieve it with this function?

THis is the M code:

//"Changed Type"
let
    Source = Excel.Workbook(File.Contents("C:\Users\lionelch\Desktop\x2.xlsx"), null, true),
    Sheet5_Sheet = Source{[Item="Sheet5",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet5_Sheet, [PromoteAllScalars=true]),

    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Dtae/time", type date}})

in
    #"Changed Type"

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks Lionel.

Ill give this method a try and post back.

[zoloturu · Accepted solution]

Hi @Anonymous ,

let
    Source = CData.REST.DataSource("CDATA_CONNECTOR", null, [Query=null]),
    REST_Schema = Source{[Name="REST",Kind="Schema"]}[Data],
    Parts_v1.1_Table = REST_Schema{[Name="Parts_v1.1",Kind="Table"]}[Data],
    #"Filtered Rows" = Table.SelectRows(Parts_v1.1_Table, each [Fonto_effectiveDate] = Para_Date),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Fonto_documentId] = Para_Doc),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows1",{{"Fonto_effectiveDate", type date}})
in
    #"Changed Type"

Regards,
Ruslan Zolotukhin (zoloturu)
BI Engineer at Akvelon Inc. / Kharkiv Power BI User Group Leader / DAX & Power BI Trainer
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
It was useful? Press Thumbs Up!

You are from Ukraine? If yes then welcome to Power BI User Group - KhPUG website. Other country? Check and find proper one - Power BI User Groups

[Anonymous]

Hi Ruslan

I have test your amended M Script code, when i pass the parameters back from PBI Services using URL its not excepting the paramter when using the date column as a date. Previous i had the date column field as text and it passed back 3 of the 4 parameters. 

Not sure if the above statement helps or if you have experienced similiar problems when passing URL parameters back from PBI Services. 

[Anonymous]

thanks Ruslan

for the feedback, just trying this out.