Forum Discussion

Ciapek's avatar
Ciapek
New Member
8 years ago

Convert string to date, problem with format date.

Convert string to date, problem with date format . Hi All, I have problem with correctly convert value "string" to "date". Step by step.... 1. I have 3 columns (DAY, MONTH, YEAR) - type: TEXT...
v-piga-msft's avatar
v-piga-msft
Icon for Resident Rockstar rankResident Rockstar
8 years ago

Hi Ciapek,

 

I have made a test with the Windows 7 x64 and the latest Version of Power BI Desktop(Version: 2.56.5023.1021 64-bit (March, 2018)), everything works as expected.

 

 

Please check if you have change the type of "Month", Day", "Year" to Text before you Add costom column. 

 

Date is not convert correctly. I have MM.DD.RRRR for example
2.13.2018r. what causes error in table.

 

Could you pasted the details of error so that we could help further investigate on it?

 

In addition, you could refer to my test pbix file. 

 

Best Regards,

Cherry

  • Ciapek's avatar
    Ciapek
    New Member
    8 years ago

    Hi Cherry,
    Today I checked Your solution on my computer (Office 2016).
    Everything working correktly. Tomorrow I will check your proposition on second computer.

    It working:
    =Table.AddColumn(#"Zmieniono typ1", "Niestandardowe", each [Day]&"-"&[Month]&"-"&[Year])


    But solution for example below on second computer not working.
    = Table.CombineColumns(#"Zmieniono typ2",{"Day", "Month", "Year"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Niestandardowe")

    Returns date in format: MM-DD-YYYY

    Note, I have using the same columns for each question.
    Column order with both questions is the same.
    [Day] [Month] [Year]
    But I have different results :-(.
    I thinking why?

     

    I willl let You know.

    Thank You for Your help:-)

    • Ciapek's avatar
      Ciapek
      New Member
      8 years ago

      Today I tried your solution.
      1. Works correctly on my computer.
      2. Not works on second computer.
      The problem there is  when I makes convert string to date.
      The regional setting are the same on both computers.

       

      My temporary solution is changing places numbers in string...
      I connects the chain: [M]&"-"&[D]&"-"&[Y]
      And i have correctly date.