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
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
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 ContentRegardless, 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 ParseExcelGood 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
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