Forum Discussion

vishwanathans's avatar
vishwanathans
Helper II
5 years ago
Solved

Convert data in 2 cells into date

Hi All,

 

I am just thinking through if there is any other way to convert the data below in to a date.

 

Source Data

Jan

Feb

Jan

Source Data

FY19

FY19

FY20

Target

Jan-2019

Feb-2019

Jan-2020

 

Basically i need this data type to drag it to filter field and play around the data.

 

Thanks in advance,

 

Srinivasan Vishwanathan

 

Need Help
  • vishwanathans's avatar
    vishwanathans
    5 years ago

    Hi Fowmy,

     

    For some reason i cannot share the screen shot here.

     

    But yes its the same way you have it up there.

     

    I need a result column to have (Jan-20 & Jan-19).

     

    Thanks for your help.

  • Fowmy's avatar
    Fowmy
    5 years ago

    vishwanathans 

    If you need a dax solution, go to your table in the model and under Home Tab, click on New Column and the following code, make sure you rename table and column names as per your table:

     

    Month Year = DATEVALUE("01-" & Period[month] & "-"& RIGHT(Period[year],2))

     


    If you want to do it in Power Query then
    Go to Power Query, create a new blank query and paste the below code on the advanced editor and check the step:

     

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMU9JRcos0MlCK1YlWcktNAnMNLZViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [month = _t, year = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"month", type text}, {"year", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each  Date.From("01-" & [month] & "-" & Text.End([year],2) )),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type date}})
in
    #"Changed Type1"

     

     

     



     

7 Replies

  • ChrisMendoza's avatar
    ChrisMendoza
    Resident Rockstar
    5 years ago

    I would say create a Calendar Table and relate it to your 'Source Data'.

    • vishwanathans's avatar
      vishwanathans
      Helper II
      5 years ago

      Hi Chris,

       

      can you please help me with some link for reference. I am pretty new here. 

       

      Thanks for your help.

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    vishwanathans 

    Can you reshare the sample data in a format that you have in your table
    Is it like:

    month year
    Jan FY20
    Feb FY19
    • vishwanathans's avatar
      vishwanathans
      Helper II
      5 years ago

      Hi Fowmy,

       

      For some reason i cannot share the screen shot here.

       

      But yes its the same way you have it up there.

       

      I need a result column to have (Jan-20 & Jan-19).

       

      Thanks for your help.

      • Fowmy's avatar
        Fowmy
        Super User
        5 years ago

        vishwanathans 

        If you need a dax solution, go to your table in the model and under Home Tab, click on New Column and the following code, make sure you rename table and column names as per your table:

         

        Month Year = DATEVALUE("01-" & Period[month] & "-"& RIGHT(Period[year],2))

         


        If you want to do it in Power Query then
        Go to Power Query, create a new blank query and paste the below code on the advanced editor and check the step:

         

         

         

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMU9JRcos0MlCK1YlWcktNAnMNLZViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [month = _t, year = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"month", type text}, {"year", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each  Date.From("01-" & [month] & "-" & Text.End([year],2) )),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type date}})
in
    #"Changed Type1"