Appending multiple tabs
In this section, we are going to look at how we can append multiple tabs from the same workbook. Although there are different ways in which we can do this, we are going to use M code to append the tables together.
We are going to open a workbook with sample sales data (sample1.xlsx
) for different parts of South Africa. Each table is on a separate worksheet in the workbook and each table is the name of the place (Cape Town, Durban, and so on):
- To append the different sheets, we will create a new Blank Query by selecting this from From Other Sources in the Data tab.
- In the formula bar, I am going to use the
=Excel.CurrentWorkbook()
formula, which will show me all of the tables in the Excel workbook. Please remember that the formula is case sensitive:Looking at the formula, it tells us to look at the current workbook and then it finds the tables, name ranges, and connections, which are displayed. When...