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