CODEWITHSUNDEEP
powerquery
Andrea.Ko
Andrea.Ko

Reputation: 119

How to combine different sheets into one file given the numbers of sheets always different range/sheet name in power query

Using Power Query, i combine different sheets into 1 consolidate sheet(i.e. AllPermit).

I facing problem when the numbers of sheets each month is different depending on Permit transactions. Eg, APR - I have total of 4 sheets.

enter image description here

Is there any solution for me not to redo the power query again every month, so that it wont have errors that looking for Sheet#4? Also, when there is new sheets, it can be intelligently detected there are 5 sheets?

Any advice is very much appreciated.

Upvotes: 0

Views: 153

Answers (1)

horseyride
horseyride

Reputation: 21298

Something like this works for me, combining all tabs, all columns, all rows, on workbooks in directory

let Source = Folder.Files("C:\directory\subdirectory"),
#"Removed Other Columns" = Table.SelectColumns(Source,{"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", "Name2"}),
#"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"

Thanks to Imke for List step

Upvotes: 1

Related Questions