let
Source = Csv.Document(File.Contents("C:\temp\EAL BAHAHA ALB1714.txt"),[Delimiter=" ", Columns=1, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Column1], "CTR")),
#"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "#", 1, 1),
#"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"#", "Column1"}),
Split1 = Table.SplitColumn(#"Reordered Columns","Column1",Splitter.SplitTextByPositions({0, 9}, false),{"Delete", "Text"}),
Split2 = Table.SplitColumn(Split1,"Text",Splitter.SplitTextByPositions({0, 11}, false),{"Container No.", "Text"}),
Split3 = Table.SplitColumn(Split2,"Text",Splitter.SplitTextByPositions({0, 5}, false),{"Weight", "Text"}),
Split4 = Table.SplitColumn(Split3,"Text",Splitter.SplitTextByPositions({0, 4}, false),{"ISO", "Text"}),
Split5 = Table.SplitColumn(Split4,"Text",Splitter.SplitTextByPositions({0, 1}, false),{"Status", "Text"}),
Split6 = Table.SplitColumn(Split5,"Text",Splitter.SplitTextByPositions({0, 6}, false),{"IMO", "Text"}),
Split7 = Table.SplitColumn(Split6,"Text",Splitter.SplitTextByPositions({0, 3}, false),{"POL", "Text"}),
Split8 = Table.SplitColumn(Split7,"Text",Splitter.SplitTextByPositions({0, 2}, false),{"Delete2", "Text"}),
Split9 = Table.SplitColumn(Split8,"Text",Splitter.SplitTextByPositions({0, 3}, false),{"POD", "Text"}),
Split10 = Table.SplitColumn(Split9,"Text",Splitter.SplitTextByPositions({0, 1}, false),{"CAT", "Text"}),
Split11 = Table.SplitColumn(Split10,"Text",Splitter.SplitTextByPositions({0, 1}, false),{"Mode", "Text"}),
Split12 = Table.SplitColumn(Split11,"Text",Splitter.SplitTextByPositions({0, 3}, false),{"Line", "Text"}),
Split13 = Table.SplitColumn(Split12,"Text",Splitter.SplitTextByPositions({0, 1}, false),{"Rfr Sts", "Text"}),
Split14 = Table.SplitColumn(Split13,"Text",Splitter.SplitTextByPositions({0, 1}, false),{"Delete3", "Temp"}),
#"Removed Columns" = Table.RemoveColumns(Split14,{"Delete", "Delete2", "Delete3"})
in
#"Removed Columns"
This query will transform your SPARCS system file:
let
Source = Csv.Document(File.Contents("C:\temp\SPARCS BAHAHA ALB1714.txt"),[Delimiter=" ", Columns=26, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{
{"Equipment ID", "Container No."},
{"Wgt-MTn", "Weight"},
{"Type", "ISO"},
{"Sts", "Status"}
}),
#"Added Mode" = Table.AddColumn(#"Renamed Columns", "Mode", each if [Inbound Carrier] = "TRUCK" then "T" else "R"),
#"Added RfrSts" = Table.AddColumn(#"Added Mode", "Rfr Sts", each if [Temp]="" then "N" else "Y"),
#"Removed Other Columns" = Table.SelectColumns(#"Added RfrSts",{"Line", "POD", "Container No.", "ISO", "IMO", "Temp", "Cat", "POL", "Weight", "Status", "Mode", "Rfr Sts"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([#"Container No."] <> "")),
#"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 1, 1),
#"Renamed Index" = Table.RenameColumns(#"Added Index",{{"Index", "#"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Index",{"#", "Container No.", "Weight", "ISO", "Status", "IMO", "POL", "POD", "Cat", "Mode", "Line", "Rfr Sts", "Temp"})
in
#"Reordered Columns"
In both cases, just change the file path / name (in red) to suit.
Bookmarks