Power Query adding sequential columns

[jbot]

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 trigger would be whenever a new workbook is uploaded to a SharePoint folder and the query is refreshed. I am able to create a query to overwrite the column I am wanting to upload to but i can't figure out how to make the query upload the new data to the column directly to the right of the previously uploaded column while still keeping the previously uploaded data in the column to the left of the new data. Basically, I want an automated solution for copy pasting one column of data into one spreadsheet as soon as the workbooks are uploaded to a sharepoint folder. 

Any help is appreciated!

Thank you.

[MarkLaf · Accepted solution]

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

[v-nmadadi-msft]

Hi @jbot 

May I ask if you have resolved this issue by the reply provided by @MarkLaf ? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[MarkLaf]

You'll want to connect to the folder with all the workbooks, transform them all at once in a list so that you've got the list of single columns from each workbook, then convert that into a table. As new workbooks are added into the folder, new columns will get added.

Here is the M with some examples. I'm assuming that the data are of different shapes in the workbooks.

sample1.xlsx

Column A	Column B	Column C
1	abc2	0
2	jwc731	10
3	ova249	20
4	ath260	30
5	plm111	40

sample2.xlsx

Column A	Column C
5	100
4	1000
2	10000
1	100000
0	1000000

sample3.xlsx

Column A	Column C
1	100000
o	1000000

FYI these are all just entered simply in Sheet1 of each workbook:

Now, I start with just samples 1-2 in a local folder:

Here is M that converts this into two columns:

let
    Source = Folder.Files("<local folder location with xlsx>"),

    //main initial action is to parse workbooks into a list
    ParseExcel = List.Transform( Source[Content], Excel.Workbook ), 

    //now we can do whatever series of transformations we need that will
    //convert each list item / workbook into a single list (column) of data
    WhateverTransformsToOneColEach = 
    //I'm doing a bunch of transforms at once here,
    //but you can take whatever approach you want.
    //The main thing is that the output needs to be 
    //a list of column data per workbook
    List.Transform( 
        ParseExcel, 
        each let 
            openSheet1 = _{[Name="Sheet1"]}[Data],
            promoteHeaders = Table.PromoteHeaders( openSheet1 ),
            tableToCols = Table.ToColumns( promoteHeaders ),
            combineColsIntoOne = List.Combine( tableToCols )
        in
            combineColsIntoOne
    ),

    ToTable = Table.FromColumns( WhateverTransformsToOneColEach )

in
    ToTable

What I mean by "output needs to be a list of column data per workbook" - this is the output of my WhateverTransformsToOneColEach step showing preview of each workbook output / list item:

Output on load (including some random data in columns to left to mimic your setup):

Now, I'll drop in sample3.xlsx

And hit refresh in my workbook - the parsing in our query, the <WhateverTransformsToOneColEach> part, needs to be able to handle whatever shape sample3.xlsx or any new workbooks will be in:

[jbot]

Hello,

Thank you for looking into my issue. I am not very familar with M, the ParseExcel function you used gives me an error when I try it in my query. Is it a custom function I have to create beforehand?

Thank you. 

[MarkLaf]

You may have to tweak the code depending on how you are connecting to your data. The key thing, though, is that you have to connect to the folder/directory that contains your files rather than connect individually to each file.

So, the key two steps to focus on to get working in your scenario:

    Source = Folder.Files("<local folder location with xlsx>"),

    ParseExcel = List.Transform( Source[Content], Excel.Workbook ), 

The first step, Source, is connecting to a folder on my local machine. This should be switched out to the appropriate connection (e.g. SharePoint Folder, etc.)

In my example, the output of my Source step looks like:

This snip of the output structure above is key to understanding what happens in the next step, PraseExcel. For the first argument of List.Transform, we reference the Content column containing the Excel binaries with Source[Content]. This is where you may have to tweak depending on your particular connection. The second argument of List.Transform is just calling the Excel.Workbook function, which is the main built-in function for parsing an Excel binary.

If you share your anonymized M, then people can provide more specific guidance.

[jbot]

Thank you for the response. Please see below what I have Tried from your code. ParseExcel is still not being accepted. I tried to takle it out and just do the List.Transform function that didnt have any sytax error but didnt provide the results either. 

with parse excelwithout parse excelwithout parseexcel result

[MarkLaf]

Are you not seeing Binary in the Content column? In your last snip, it looks like the Content field of "Test File" is FALSE rather than Binary. Actually, if that is the same as the full Advanced Editor snip, then the issue is that you actually have one step, #"Test File" that is doing a comparison between Shared Docs content and the bad transform on Source. I.e. despite the line break, you are actually doing:

#"Test File" = #"Shared Documents"{[Name="Test File"]}[Content] = List.Transform( Source[Content], Excel.Workbook ) //shorthand: Test File Content = Excel Parse on Source Content

Regardless, in the Advanced Query windows you snipped, it shows that Source is connecting to you site collection and it's #"Shared Documents" that is connecting to your library/folder with the Excel docs. So, that should be used in the first argument of List.Transform, not Source.

Also, if you happen to have any non-xlsx in the folder, be sure to filter them out first.

So, probably, your M should look something like:

let
    Source = SharePoint.Contents(" <site url> ", [ApiVersion = 15]),
    #"Shared Documents" = Source{[Name="Shared Documents"]}[Content],

    ParseExcel = List.Transform( #"Shared Documents"[Content], Excel.Workbook )
in
    ParseExcel

[jbot]

Good News! The ParseExcel worked as seen in first pic. (i was missing commas 😐 )  Thank you for your support and patient thus far. The next hurdle is transforming my raw data before it is parsed and combined with the other workbooks in the folder. Pic 2 shows my raw data imported from the workbook. I only want column 6 and then all "null" values from column 6 I want filtered out. The result of that transform is seen in pic 3. Im not sure how to code the query to perform these transforms first and then run the parse code to combine only column 6 from each workbook. 

Thank you again. Good Parse code and result!Raw workbook dataTransformed workbook data ready to be parsed

[MarkLaf · Accepted solution]

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

[jbot]

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. 

[MarkLaf · Accepted solution]

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]

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]

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

[jbot]

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.

[v-nmadadi-msft]

Hi @jbot,

May we know how exactly you are creating the query to overwrite the column, based on that information we will look for possible suggestions to reorder the column in correct way.

Also in this forum you will find people who are good at Fabric and Power BI if you believe your query can be resolved by people who are good at Excel and who have experience using powerquery within Excel, you can utilize Excel community: Welcome to the Excel Community | Microsoft Community Hub 

Thanks and Regards

[jbot]

Hello, 

Thank you for the response. 

I have uploaded a few pictures to show what I am trying to accomplish. The first picture shows shows the close and load to operation and the destination of the first imported column. The second picture shows the first file in the sharepoint folder being imported. There is only one file in the sharepoint folder at this point. This result is good. The problem comes when I add another excel file to the sharepoint folder. I manually added file "M1-8" to the folder to replicate what our system will be doing automatically. I then manually refreshed the query and the result showed in third picture that the query loaded the new file on top of my old file loaction while moving the previous column of data down to the bottom as shown in fourth picture. I desire the new data column to be uploaded to the column to the right of the previous column once a new file is added to the sharepoint folder. 

I also uploaded a picture of the query editor page (picture 5). 

Thank you for the help.

Josh Query Load ToQuery Load to ResultM1-8 file AddedM1-8 file added 2query

[SamanthaPuaXY]

Hi @jbot , I don't think that's possible by creating multiple tables/power query.
You could Read all the files (assuming they contain same data), and do a pivot to make the file name the header and load them into excel. This way all new files will appear as a new column to the right of existing columns.

Better if you have a sample/screenshot of what you are trying to achieve

[jbot]

Please see my reply to v-nmadadi-msft for more content on what I am trying to achieve.

[Omid_Motamedise]

Hi, 
Can you provide an example for your data?

[jbot]

Please see my reply to v-nmadadi-msft for more content on what I am trying to achieve.