Forum Discussion

Frequent Visitor

1 year ago

Solved

Power Query adding sequential columns

Hello, I am trying to use Power Query within Excel to automatically load a single column of data from a workbook into the column directly to the right of the column that was previously uploaded. The...

Need Help

MarkLaf

1 year ago

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

MarkLaf

1 year ago

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

MarkLaf

Super User

1 year ago

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

jbot

Frequent Visitor

1 year ago

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

MarkLaf

Super User

1 year ago

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):

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

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

Forum Discussion

Power Query adding sequential columns

Related content

Sequential Order column based on multiple criteria

Adding columns

Adding a column

Renaming columns and adding columns

Sequential Order column based on multiple criteria