question

JosephMcCartney-3236 avatar image
0 Votes"
JosephMcCartney-3236 asked ·

Copy date in columns

![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]: /answers/storage/attachments/75553-image.png

office-excel-itpro
image.png (17.6 KiB)
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Lz-3068 avatar image
0 Votes"
Lz-3068 answered ·

Hi @JosephMcCartney-3236 (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

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Lz-3068 avatar image
1 Vote"
Lz-3068 answered ·

Hi @JosephMcCartney-3236

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

PIC

Corresponding workbook avail. here



demo.png (65.1 KiB)
· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@JosephMcCartney-3236
Please refer to Lz-3068 reply.
Any questions, you may post back.

0 Votes 0 ·
JosephMcCartney-3236 avatar image
0 Votes"
JosephMcCartney-3236 answered ·

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



·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JosephMcCartney-3236 avatar image
0 Votes"
JosephMcCartney-3236 answered ·

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

· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Glad I could help @JosephMcCartney-3236 & Thanks for posting back (I'm not a genuis either :)

0 Votes 0 ·