Microsoft Office Forums - View Single Post

Alansidman · 05-23-2019, 05:31 PM

Using Power Query which is available to you since you indicate you are using 2016. It is on the Data Tab and shown as Get and Transform.

Here is the Mcode

Code:

let
    Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\Users\alans\Desktop\Food_Lion_US_V.csv.txt"), null, null, 1252)}),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 3), type number),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Inserted Modulo", {{"Modulo", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Inserted Modulo", {{"Modulo", type text}}, "en-US")[Modulo]), "Modulo", "Column1"),
    #"Filled Up" = Table.FillUp(#"Pivoted Column",{"2", "1"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([0] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"0", "Location"}, {"1", "City"}, {"2", "Phone"}})
in
    #"Renamed Columns"

The file is attached.