Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
jbot
Frequent Visitor

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 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.

2 ACCEPTED SOLUTIONS
MarkLaf
Memorable Member
Memorable Member

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

 

View solution in original post

MarkLaf
Memorable Member
Memorable Member

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

View solution in original post

17 REPLIES 17
v-nmadadi-msft
Community Support
Community Support

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
Memorable Member
Memorable Member

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 AColumn BColumn C
1abc20
2jwc73110
3ova24920
4ath26030
5plm11140

 

sample2.xlsx

Column AColumn C
5100
41000
210000
1100000
01000000

 

sample3.xlsx

Column AColumn C
1100000
o1000000

 

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

MarkLaf_0-1740207579207.png

 

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

MarkLaf_1-1740207722676.png

 

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:

MarkLaf_2-1740208729883.png

 

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

MarkLaf_3-1740208872827.png

 

Now, I'll drop in sample3.xlsx

MarkLaf_4-1740208942667.png

 

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:

MarkLaf_5-1740209071299.png

 

 

jbot
Frequent Visitor

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
Memorable Member
Memorable Member

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:

MarkLaf_0-1740779977672.png

 

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
Frequent Visitor

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 excelwith parse excelwithout parse excelwithout parse excelwithout parseexcel resultwithout parseexcel result

MarkLaf
Memorable Member
Memorable Member

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
Frequent Visitor

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!Good Parse code and result!Raw workbook dataRaw workbook dataTransformed workbook data ready to be parsedTransformed workbook data ready to be parsed

MarkLaf
Memorable Member
Memorable Member

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
Frequent Visitor

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. date created.pngadd as new row.png

MarkLaf
Memorable Member
Memorable Member

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

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
Community Support
Community Support

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

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 ToQuery Load to ResultQuery Load to ResultM1-8 file AddedM1-8 file AddedM1-8 file added 2M1-8 file added 2queryquery

SamanthaPuaXY
Helper II
Helper II

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

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

Omid_Motamedise
Super User
Super User

Hi, 
Can you provide an example for your data?

If my answer helped solve your issue, please consider marking it as the accepted solution. It helps others in the community find answers faster—and keeps the community growing stronger!
You can also check out my YouTube channel for tutorials, tips, and real-world solutions in Power Query with the following link
https://youtube.com/@omidbi?si=96Bo-ZsSwOx0Z36h

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

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.