Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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.
Solved! Go to 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
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
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:
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.
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.
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 excel
without parse excel
without parseexcel result
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
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 data
Transformed workbook data ready to be parsed
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
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.
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
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.
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 To
Query Load to Result
M1-8 file Added
M1-8 file added 2
query
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.
Hi,
Can you provide an example for your data?
Please see my reply to v-nmadadi-msft for more content on what I am trying to achieve.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
13 | |
8 | |
8 | |
7 |
User | Count |
---|---|
17 | |
11 | |
7 | |
6 | |
6 |