Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Surya1
Helper I
Helper I

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?

1 ACCEPTED SOLUTION

@Surya1

 

Please see attached file

 

Time.png


Regards
Zubair

Please try my custom visuals

View solution in original post

6 REPLIES 6
Zubair_Muhammad
Community Champion
Community Champion

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

Regards
Zubair

Please try my custom visuals

@Surya1

 

Please see attached file

 

Time.png


Regards
Zubair

Please try my custom visuals

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

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


Regards
Zubair

Please try my custom visuals

the join should be performed using the query editor?

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


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.