Power Query adding sequential columns

That did it! Thank you very much for all the help! I will accept that last message as the solution, I do have one more request if you dont mind looking into. I noticed my raw data isnt actually showing the date of the file. It would be great if the date created could be added to the top of each column. Like shown in the pictures below. It could be a new row added to the top and I can just remove the date row i have currently.

Thank you. 

You're a wizard! Thank you that worked. I'm getting more comfortable with the coding now too. I will definitely reach out again if i have anymore issues.

Best regards.

Hello Mark, 

I am back with a few more asks regarding my code for this query. How do I add another column within each xlsx file to the output. I currently only have column 2 parsed out. I want to add Column 6 to the right of Column 2 in the query output. 

Please see the code I have tried and the picture of what I am wanting to achieve. Column 2 is what is shwoing in the picture. 

Thanks 

let
    Source = SharePoint.Contents("https://cmgaero.sharepoint.com/sites/Dept514SPCAnalysis/", [ApiVersion = 15]),
    #"Shared Documents" = Source{[Name="Shared Documents"]}[Content],
    #"Test File" = #"Shared Documents"{[Name="Test File"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(#"Test File",{{"Date created", type datetime}, {"Date modified", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date created", Order.Descending}}),

    //perform the transformation on the xlsx, 
    //output will be a list of Column6's from all xlsx
    ParseAndDrill1 = 
    Table.TransformRows( 
        #"Sorted Rows",
        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)
            GetCol2 = OpenStudy1Sheet[Column2], 
            //remove nulls, measurement results and date from the col + add Date created
            RemoveNullsAndAddDate = {[Date created]} & List.Select( GetCol2, each _ <> null ),
	    RemoveMeasurementResults = List.Select( RemoveNullsAndAddDate, each _ <> "Measurement Results"),
	    RemoveDate = List.Select( RemoveMeasurementResults, each _ <> "Date")

        in
            RemoveDate
    ),
      ParseAndDrill2 = 
    Table.TransformRows( 
        #"Sorted Rows",
        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, measurement results and date from the col + add Date created
            RemoveNullsAndAddDate = {[Date created]} & List.Select( GetCol6, each _ <> null ),
	    RemoveMeasurementResults = List.Select( RemoveNullsAndAddDate, each _ <> "Measurement Results"),
	    RemoveDate = List.Select( RemoveMeasurementResults, each _ <> "Date")

        in
            RemoveDate
  ),

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

Is the issue that your current code results in a table like (all column 2's, then all column 6's - ie sorted by columns then by workbook):

Workbook1Column2 | Workbook2Column2 | Workbook3Column2 | Workbook1Column6 | Workbook2Column6 | Workbook3Column6

But you want instead (sorted by workbook then by column):

Workbook1Column2 | Workbook1Column6 | Workbook2Column2 | Workbook2Column6 | Workbook3Column2 | Workbook3Column6

I put this together in VS code without using test data, so there may be some mistakes.

Given columns to extract may change, restructured a bit to make this easier/dynamic. Also, consolidated filters (no null, no "Measurement Results", etc.) into one List.Select

let
    Source = SharePoint.Contents("https://cmgaero.sharepoint.com/sites/Dept514SPCAnalysis/", [ApiVersion = 15]),
    #"Shared Documents" = Source{[Name = "Shared Documents"]}[Content],
    #"Test File" = #"Shared Documents"{[Name = "Test File"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(
        #"Test File", {{"Date created", type datetime}, {"Date modified", type date}}
    ),
    #"Sorted Rows" = Table.Sort(#"Changed Type", {{"Date created", Order.Descending}}),

    ColGroupToExtract = {"Column2", "Column6"}, //  <---- add dynamic input

    // perform the transformation on the xlsx, 
    // output will be a list of specified group (list) of columns from all xlsx
    // ie, output looks like: 
    // { { {Row1Col2Data},{Row1Col6Data} }, { {Row2Col2Data},{Row2Col6Data} }, ... }
    ParseAndGetColGroups = Table.TransformRows(
        #"Sorted Rows",
        (row) =>
            let
                // parse excel
                ParseExcel = Excel.Workbook(row[Content]),

                // navigate to the sheet you want
                OpenStudy1Sheet = ParseExcel{[Item = "Study1", Kind = "Sheet"]}[Data],

                // iterate on dynamic input list of columns to extract
                ExtractCols = List.Transform(
                    ColGroupToExtract,
                    each
                        let
                            // get single column you want as a list
                            GetCol = Table.Column(OpenStudy1Sheet, _),

                            // remove nulls, measurement results and date from the col
                            // and then append to Date created
                            AddDateAndRemoveBadVals = {row[Date created]}
                                & List.Select(
                                    GetCol, each 
                                    _ <> null and 
                                    _ <> "Measurement Results" and
                                    _ <> "Date"
                                )
                        in
                            AddDateAndRemoveBadVals
                )
            in
                ExtractCols
    ),

    // Table.FromColumns needs list of columns (as lists), so we need to convert
    // { { {Row1Col2Data},{Row1Col6Data} }, { {Row2Col2Data},{Row2Col6Data} }, ... }
    // to: { {Row1Col2Data},{Row1Col6Data},{Row2Col2Data},{Row2Col6Data}, ... }
    // simple list combine will achieve this
    CombineAllColGroups = List.Combine(ParseAndGetColGroups),
    ToTable = Table.FromColumns(CombineAllColGroups)
in
    ToTable