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.