Power Query adding sequential columns

Almost there, I think. Now that I have a better sense of the strucutre of your workbooks and the exact desired output, I think this should do it:

let
    //connect to your site files
    Source = SharePoint.Contents( "<your site>", [ApiVersion = 15] ),

    //navigate to folder with xlsx
    #"Shared Documents" = Source{[Name="Shared Documents"]}[Content],
    #"Test File" = #"Shared Documents"{[Name="Test File"]}[Content],

    //perform the transformation on the xlsx, 
    //output will be a list of Column6's from all xlsx
    ParseAndDrill = 
    List.Transform( 
        #"Test File"[Content],
        each let
            //parse excel
            ParseExcel = Excel.Workbook( _ ),
            //navigate to the sheet you want
            OpenStudy1Sheet = ParseExcel{[Item="Study1",Kind="Sheet"]}[Data],
            //get single column you want as a list 
            //(want this format to construct table later)
            GetCol6 = OpenStudy1Sheet[Column6], 
            //remove nulls from the col
            RemoveNulls = List.Select( GetCol6, each _ <> null )
        in
            RemoveNulls
    ),

    //with list of Column6's from all xlsx, put into table
    ToTable = Table.FromColumns( ParseAndDrill )
in
    ToTable

Yes, I think this should do it. We can use Table.TransformRows to get access to all the fields rather than just [Content]; then, all we have to do is tag [Date created] to the top of our Column6's:

let
    //connect to your site files
    Source = SharePoint.Contents( "<your site>", [ApiVersion = 15] ),

    //navigate to folder with xlsx
    #"Shared Documents" = Source{[Name="Shared Documents"]}[Content],
    #"Test File" = #"Shared Documents"{[Name="Test File"]}[Content],

    //perform the transformation on the xlsx, 
    //output will be a list of Column6's from all xlsx
    ParseAndDrill = 
    Table.TransformRows( 
        #"Test File",
        each let
            //parse excel
            ParseExcel = Excel.Workbook( [Content] ),
            //navigate to the sheet you want
            OpenStudy1Sheet = ParseExcel{[Item="Study1",Kind="Sheet"]}[Data],
            //get single column you want as a list 
            //(want this format to construct table later)
            GetCol6 = OpenStudy1Sheet[Column6], 
            //remove nulls from the col + add Date created
            RemoveNulls = {[Date created]} & List.Select( GetCol6, each _ <> null )
        in
            RemoveNulls
    ),

    //with list of Column6's from all xlsx, put into table
    ToTable = Table.FromColumns( ParseAndDrill )
in
    ToTable