Hi All,
@amitchandak
I have a date column in one of my source. The source used is a smartsheet which is filled manually. The date column in this smartsheet is populated in 2 different formats 1/10/2022 which is read correctly by Power BI as January 10,2022 but if the value is 05-04-22 it is read by Power BI as April 22,2005 whereas it should be May 4 ,2022.
I have described below what I am thinking to do, but would appreciate any ideas to format the date field consistently.
In order to format the date correctly, I was thinking of using power query and add columns to extract first 2 characters, text between delimiters and the last characters. How can I write an if statement in power query which does something like this :
If delimiter is '-' then extract between 1st and 2nd delimiter else if delimiter is '/' then extract between 1st and 2nd delimiter.
Table.AddColumn(#"Inserted First Characters", "Text Between Delimiters", each if Text.Contains([Date], "-") then each Text.BetweenDelimiters([Date], "-", "-"), type text) else if Text.Contains([Date], "/") each Text.BetweenDelimiters([Date], "/", "/"), type text)
