Forum Discussion

Surya1's avatar
Surya1
Helper I
8 years ago
Solved

substring

Can someone help me..

 

I have a column1 like below:

 

xy.xc.vc.20180302.txt

df.hgf.h.2018.03.02.txt

dfj_gf-2018.03.02.txt

 

there is another column2 with the time details

 

20180402 15:30:00

20180402  21:00:00

i want to creaate a new column appending the date value from column1 and time from column2 to make a date/time column3

 

How to do tat?

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks

6 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    Surya1

     

    Try this Column

     

    Column =
VAR NumberPosition =
    FIND ( 2, TableName[Col 1],, 0 )
VAR DotPosition =
    FIND ( ".txt", TableName[Col 1],, 0 )
VAR MyNumbers =
    SUBSTITUTE (
        MID ( TableName[Col 1], NumberPosition, DotPosition - NumberPosition ),
        ".",
        ""
    )
VAR MyYEAR =
    LEFT ( MyNumbers, 4 )
VAR MyMonth =
    MID ( MyNumbers, 5, 2 )
VAR MyDate =
    MID ( MyNumbers, 7, 2 )
VAR spaceposition =
    FIND ( " ", TableName[Col 2],, 0 )
VAR Time_Value =
    TIMEVALUE (
        MID (
            TableName[Col 2],
            spaceposition + 1,
            LEN ( TableName[Col 2] ) - spaceposition
                + 1
        )
    )
RETURN
    DATE ( MyYEAR, MyMonth, MyDate ) + TIMEVALUE ( Time_Value )
      • Surya1's avatar
        Surya1
        Helper I
        8 years ago

        Thank You.One more query.How can i add columns from two or more tables to a new a table.

        I want to add col1 from table 1 and col1 and col3 from Table2 to a new table 3 using DAX

         

        Table1                                           Table 2

         

        col1  col2 col3                         col1 col2 col3 col4 col5

         

         

         

         

        Tabl3(New)

         

        col1  col2 col3