Forum Discussion
Power Query adding sequential columns
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 ToTableYes, 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
Super UserYou'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.
- 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.
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
