I am new to PowerQuery. I have many .XLSX files with the exact same structure and within each of these files I would like to access many specific values (using their col/row position, i.e. "B1.Value") and order it in a way that the output is a table, with each row being related to one of the .XLSX files.
As of now, I have some VBA scripts that do the job by fetching these files one by one, extracting the interesting values of each file and allocating these to a new row in another workbook. Is there a way to do a similar (maybe cleaner) job in PowerQuery ? I've been searching for that quite a long time, and can't figure out any similar question.
Thanks a lot for your support !
You really need to post examples of the source file and desired output, but in general, powerquery like below will combine data from all xlsx files in a directory into one big table that you can usually parse out for what you are looking for
You can paste into home...advanced editor..
let Source = Folder.Files("C:\subdirectory\directory"),
#"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xlsx")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Name", "Content"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "GetFileData", each Excel.Workbook([Content],true)),
#"Expanded GetFileData" = Table.ExpandTableColumn(#"Added Custom", "GetFileData", {"Data", "Hidden", "Item", "Kind", "Name"}, {"Data", "Hidden", "Item", "Kind", "Sheet"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded GetFileData",{"Content", "Hidden", "Item", "Kind"}),
List = List.Union(List.Transform(#"Removed Columns"[Data], each Table.ColumnNames(_))),
#"Expanded Data" = Table.ExpandTableColumn(#"Removed Columns", "Data", List,List)
in #"Expanded Data"