substring

[Surya1]

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?

[Zubair_Muhammad]

@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 )

[Zubair_Muhammad · Accepted solution]

@Surya1

Please see attached file

[Surya1]

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

[Zubair_Muhammad]

Hi @Surya1

You must have a common key to join them

If you dont have it....You can add an Index Column from Query Editor and then join the 2 tables on it

Then you can easily combine tables using "RELATED" Dax function

[Surya1]

the join should be performed using the query editor?

[Zubair_Muhammad]

HI @Surya1

You can use  Query Editor as well

With Query Editor

1) First Add an Index Column in both Tables

2) Now join the tables using Index Column

3) Use merge queries (Transform Tab) to join the 2 Tables