locked
How to create formula to seprate values in one columns to be in two columns RRS feed

  • Question

  • HI experts ,

    I have the following below format rows for a data source and I want to separate the time in and out in new two different columns to be as target format  

    original

    1/10/2016 2:46:20 PM out
    1/10/2016 7:15:08 AM in

    Target Format

    Out Time       In Time           Day        Month
    2:46:20 PM    7:15:08 AM       10          1

    your support please

    Thursday, December 22, 2016 3:32 PM

Answers

  • Hi SQL Kitchen,

    You can split the string by space delimiter. I try to reproduce your scenario and get expected result.

    First, import your data by Query, and click the Edit, you will get the Power Query Edit.

    Click the split column-> Space->OK, the string will be splitted several parts as follows. 




     

    Then click the Second and third columns-> click Merge as the following acreenshot.


    Get the following result.



    Then Select Values.4 column->Click Transform-->Pivot Columns, Expand the Advanced Options. In the drop down "Aggregate Value Function" select "Don't Aggregate".



    Get result as the screenshot below.



    Finally, split the First the column, remove the year columns, rename the header name, move the column position. You will get expected result.



    In addition, you also run the following Power Query statement to get what you want.

    let
        Source = Excel.Workbook(File.Contents("C:\Users\XXXX\Desktop\New Microsoft Excel Worksheet (3).xlsx"), null, true),
        Table7_Table = Source{[Item="Table7",Kind="Table"]}[Data],
        #"Changed Type" = Table.TransformColumnTypes(Table7_Table,{{"Values", type text}}),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type","Values",Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv),{"Values.1", "Values.2", "Values.3", "Values.4"}),
        #"Merged Columns" = Table.CombineColumns(#"Split Column by Delimiter",{"Values.2", "Values.3"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
        #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Values.4]), "Values.4", "Merged"),
        #"Split Column by Delimiter1" = Table.SplitColumn(#"Pivoted Column","Values.1",Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv),{"Values.1.1", "Values.1.2", "Values.1.3"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Values.1.1", Int64.Type}, {"Values.1.2", Int64.Type}, {"Values.1.3", Int64.Type}}),
        #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"Values.1.3", "out", "in", "Values.1.2", "Values.1.1"}),
        #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Values.1.2", "Day"}, {"Values.1.1", "Month"}}),
        #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Values.1.3"})
    in
        #"Removed Columns"
    IF you have any other issue, please let me know.


    Best Regards,
    Angelia


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, December 23, 2016 3:15 AM
  • Hello, 

    I suggest to bring your original data into Power Query first for a quick ETL and then model the data in Power Pivot.  

    Power Query: 

    1. Transpose 
    2. Reverse Rows
    3. Promote Headers


    Power Pivot:

    Create two columns:

    1. Day=DAY([In])
    2. Month=MONTH([In])

    You end up with 4 columns which will have to be brought into the excel one by one as four separate pivot tables. Last thing that remains to do is to rename the columns  

    Thanks, 
    Nick -

    Friday, December 23, 2016 3:29 AM

All replies

  •            

    HI experts ,

    I am using Excel 2013 power Pivot, I have the below format rows for a data source and I want to separate the time in and out in new two different columns to be as target format  

    original

    1/10/2016 2:46:20 PM out
    1/10/2016 7:15:08 AM in

    Target Format

    Out Time       In Time           Day        Month
    2:46:20 PM    7:15:08 AM       10          1

    your support please


    • Edited by SQL Kitchen Friday, December 23, 2016 12:01 AM
    • Merged by Chenchen Li Friday, December 23, 2016 3:12 AM duplicate
    Friday, December 23, 2016 12:00 AM
  • Hi,

    Since your issue is about Power Pivot for Excel, I suggest you ask in Power Pivot forum.

    And I notice you have created a thread in the PowerPivot forum, so I would merge this thread into that one.

    Thanks for your understanding.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 23, 2016 3:10 AM
  • Hi SQL Kitchen,

    You can split the string by space delimiter. I try to reproduce your scenario and get expected result.

    First, import your data by Query, and click the Edit, you will get the Power Query Edit.

    Click the split column-> Space->OK, the string will be splitted several parts as follows. 




     

    Then click the Second and third columns-> click Merge as the following acreenshot.


    Get the following result.



    Then Select Values.4 column->Click Transform-->Pivot Columns, Expand the Advanced Options. In the drop down "Aggregate Value Function" select "Don't Aggregate".



    Get result as the screenshot below.



    Finally, split the First the column, remove the year columns, rename the header name, move the column position. You will get expected result.



    In addition, you also run the following Power Query statement to get what you want.

    let
        Source = Excel.Workbook(File.Contents("C:\Users\XXXX\Desktop\New Microsoft Excel Worksheet (3).xlsx"), null, true),
        Table7_Table = Source{[Item="Table7",Kind="Table"]}[Data],
        #"Changed Type" = Table.TransformColumnTypes(Table7_Table,{{"Values", type text}}),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type","Values",Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv),{"Values.1", "Values.2", "Values.3", "Values.4"}),
        #"Merged Columns" = Table.CombineColumns(#"Split Column by Delimiter",{"Values.2", "Values.3"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
        #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Values.4]), "Values.4", "Merged"),
        #"Split Column by Delimiter1" = Table.SplitColumn(#"Pivoted Column","Values.1",Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv),{"Values.1.1", "Values.1.2", "Values.1.3"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Values.1.1", Int64.Type}, {"Values.1.2", Int64.Type}, {"Values.1.3", Int64.Type}}),
        #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"Values.1.3", "out", "in", "Values.1.2", "Values.1.1"}),
        #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Values.1.2", "Day"}, {"Values.1.1", "Month"}}),
        #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Values.1.3"})
    in
        #"Removed Columns"
    IF you have any other issue, please let me know.


    Best Regards,
    Angelia


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, December 23, 2016 3:15 AM
  • Hello, 

    I suggest to bring your original data into Power Query first for a quick ETL and then model the data in Power Pivot.  

    Power Query: 

    1. Transpose 
    2. Reverse Rows
    3. Promote Headers


    Power Pivot:

    Create two columns:

    1. Day=DAY([In])
    2. Month=MONTH([In])

    You end up with 4 columns which will have to be brought into the excel one by one as four separate pivot tables. Last thing that remains to do is to rename the columns  

    Thanks, 
    Nick -

    Friday, December 23, 2016 3:29 AM