Copy date in columns

Joseph McCartney 21 Reputation points
2021-03-08T20:28:17.137+00:00

![75553-image.png][1] How do I take the data in the I column and copy it below as a seperate line item. I want Davidson to look like Community School, with the school name on each line and a SN oneach line. [1]: /api/attachments/75553-image.png?platform=QnA

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,627 questions
0 comments No comments
{count} votes

Accepted answer
  1. Lz._ 8,981 Reputation points
    2021-03-09T15:16:20.243+00:00

    Hi @Joseph McCartney (the expert badge is given by this site - I'm not an expert at all though)

    NB re. the text file you uploaded:

    • the last 2 records are almost empty
    • the last 2 columns ([ANC Sorted] & [Installed]) are empty

    Did not know what to do with them ==> Kept everything

    Workbook with the query is avail. here. Query code (you'll need to change the path to the file - with the Power Query Editor > Advanced Editor - line #3):

    let  
        Source = Csv.Document(  
            File.Contents("D:\Lorenzo\Downloads\tracking-for-labels.txt"),  
            [Delimiter="	", Columns=15, Encoding=1252, QuoteStyle=QuoteStyle.Csv]  
        ),  
        PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),  
        ChangedTypes = Table.TransformColumnTypes(PromotedHeaders,  
            {  
                {"Charlotte ", type text}, {"QTY", Int64.Type}, {"CMS PO", Int64.Type}, {"Total", Currency.Type},  
                {"FRN", type text}, {"ANC PO", Int64.Type}, {"Disty", type text}, {"PO Date ", type date},  
                {"Carrier", type text}, {"Tracking #", Int64.Type}, {"ETA", type date}, {"Serial Numbers", type text},  
                {"Delivered", type date}, {"ANC Sorted", type text}, {"Installed", type text}  
            }  
        ),  
        SerialList = Table.AddColumn(ChangedTypes, "SerialList", each  
            if Text.StartsWith([Serial Numbers],"SN:")  
            then List.Skip(Text.Split([Serial Numbers],"SN: "))  
            else {[Serial Numbers]},  
            type list  
        ),  
        RemovedSerialNumbers = Table.RemoveColumns(SerialList,{"Serial Numbers"}),  
        ExpandedSerialList = Table.ExpandListColumn(RemovedSerialNumbers, "SerialList"),  
        TrimmedSerials = Table.TransformColumns(ExpandedSerialList,  
            {{"SerialList", Text.Trim, type text}}  
        ),  
        RenamedSerialList = Table.RenameColumns(TrimmedSerials, {{"SerialList","Serial Numbers"}}),  
        ReorderedColumns = Table.ReorderColumns(RenamedSerialList, Table.ColumnNames(ChangedTypes))  
    in  
        ReorderedColumns  
    

    If you need some adjust. (i.e. re. the last 2 columns) let me know

    0 comments No comments

3 additional answers

Sort by: Most helpful
  1. Lz._ 8,981 Reputation points
    2021-03-09T03:12:06.633+00:00

    Hi @Joseph McCartney

    With Get & Transform aka Power Query (easy & efficient):

    PIC

    Corresponding workbook avail. here

    1 person found this answer helpful.

  2. Joseph McCartney 21 Reputation points
    2021-03-09T13:37:44.097+00:00

    Thats easy for you, you are an expert. I tried but cannot get it done. I attached the file. any help is greatly appreciated.
    75905-tracking-for-labels.txt

    0 comments No comments

  3. Joseph McCartney 21 Reputation points
    2021-03-10T00:13:04.71+00:00

    Thank you so much!!! You are a genius! You are great!!!!!!!!!!!!!!