Power Query

[jpalleyne]

Hi there,

I have an excel file with multiple sheets and I’m trying to use power query to combine those sheets but I’m having difficulty. I know of the formula =Excel.CurrentWorkbook() but its not working for me because the example I saw it looks as if the data has to be in excel tables. Is there an easier way to combine all sheets using power query?

 

[rjmdc]

firstly all data must be converted to tables or into named ranges
save each table/range as a connection only - you can transform each table after it is in PQ or after the final append
after you have all the connections
use pull down on get data to choose append queries or right click on any query in the queries connections and choose append
in the open dialog box choose 3 or more if you have many tables to combine
then choose which you want to combine and add
change the order if necessary by moving up or down in the choice box
then press ok and query opens
perform final transformations
close and load to

 

[jpalleyne]

firstly all data must be converted to tables or into named ranges
save each table/range as a connection only - you can transform each table after it is in PQ or after the final append
after you have all the connections
use pull down on get data to choose append queries or right click on any query in the queries connections and choose append
in the open dialog box choose 3 or more if you have many tables to combine
then choose which you want to combine and add
change the order if necessary by moving up or down in the choice box
then press ok and query opens
perform final transformations
close and load to

I have 50 plus tabs in one workbook. Is it possible to convert all tabs with information into table format without going into each tab and manually formatting?

 

[rjmdc]

power query only accepts tables or named range
perhaps the forum can assist you with a code

i once had a workbook with over 100 sheets each with different name and also different data setup and columns
i created one sheet divided by names and then all info listed underneath
this is the code i used i am not sure this is what you want

Sub combine()
Dim c As Integer

Dim OffsetRows As Long

On Error Resume Next
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "combined"

For c = 2 To Sheets.Count
Sheets(c).Activate
currentsheet.Select
Range("A1").Select

Set mylastcell = Cells(1, 1).SpecialCells(xlLastCell)
mylastcelladd = Cells(mylastcell.Row + 1, mylastcell.Column).Address
myrange = "A1:" & mylastcelladd
Range(myrange).Select
Selection.Copy

If c = 2 Then
OffsetRows = 0
Else
OffsetRows = 2
End If
Sheets(1).Range("A" & Rows.Count).End(xlUp).Offset(OffsetRows, 0).Value = Sheets(c).Name

Selection.Copy Destination:=Sheets(1).Range("A" & Rows.Count).End(xlUp).Offset(2, 0)
Next
End Sub

 

[Matador56]

You could import this from an external Excel file where you would be able to Import from Excel. This way it doesnt need to be in tables and you can multi select your sheets on the import step. However you will potentially need to perform some basic data cleansing after import if everything was not immaculate in the sheets itself. It should still be a much faster solution if you do not need the data model in the same excel workbook as the data itself?

 

[jpalleyne]

power query only accepts tables or named range
perhaps the forum can assist you with a code

i once had a workbook with over 100 sheets each with different name and also different data setup and columns
i created one sheet divided by names and then all info listed underneath
this is the code i used i am not sure this is what you want

Sub combine()
Dim c As Integer

Dim OffsetRows As Long

On Error Resume Next
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "combined"

For c = 2 To Sheets.Count
Sheets(c).Activate
currentsheet.Select
Range("A1").Select

Set mylastcell = Cells(1, 1).SpecialCells(xlLastCell)
mylastcelladd = Cells(mylastcell.Row + 1, mylastcell.Column).Address
myrange = "A1:" & mylastcelladd
Range(myrange).Select
Selection.Copy

If c = 2 Then
OffsetRows = 0
Else
OffsetRows = 2
End If
Sheets(1).Range("A" & Rows.Count).End(xlUp).Offset(OffsetRows, 0).Value = Sheets(c).Name

Selection.Copy Destination:=Sheets(1).Range("A" & Rows.Count).End(xlUp).Offset(2, 0)
Next
End Sub

Thanks

 

[jpalleyne]

You could import this from an external Excel file where you would be able to Import from Excel. This way it doesnt need to be in tables and you can multi select your sheets on the import step. However you will potentially need to perform some basic data cleansing after import if everything was not immaculate in the sheets itself. It should still be a much faster solution if you do not need the data model in the same excel workbook as the data itself?

How would you do this? And no power query can be in a separate file.