Thursday, January 30, 2020

How to Convert MMM-YY string to Date Format using M Query in Power BI

How to use M-Query to Convert MMM-YY string to Date Format in Power Query
Example :
Suppose we have sample "Customers" table as follows..


Now from the above DateString column we will generate the Custom Column "Date" using the below M Query in Power Query :
Date.FromText("01/" & Text.Start([DateString],3) & "/20" & Text.End([DateString],2))


Notes :
The M Query Text.Start function is equivalent to the LEFT Function in Power BI, and the M Query Text.End function is equivalent to the RIGHT Function in Power BI.

Result :

Finally change the DataType of  "Date" column to Date format from the Transform Tab of the Power Query.

Notes :
#date([#'Transaction_Year']',[#'Transaction_Month'],[#'Transaction_Day']) returns the Date.
Date.MonthName(#date) returns the Month Name.
Number.ToText([#'Transactions_Number') converts number to the text format.

-------------------------------------------------------------------------------------------------------- 
Thanks, TAMATAM ; Business Intelligence & Analytics Professional 
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.