Copy date in columns

Question

![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

Answer 1

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

Answer 2

Hi @Joseph McCartney

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

Corresponding workbook avail. here

Answer 3

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

Answer 4

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