Combining x number of sheets from a single Excel workbook in M

[craigpowell]

I have an Excel file with multiple sheets using the same structure that I need to append togeter in M.

Each month there is a new sheet added to the file so I need to develop a script able to append all sheets in the file without hardcoding anything.

I have been able (via some Googling) to combine workbooks and Excel tables, however I haven't been able to append sheets that aren't defined as Excel tables. 

Any help would be greatly apprecited.

[MarkLaf · Accepted solution]

The following post covers the solution: https://community.powerbi.com/t5/Desktop/Multiple-sheets-with-the-same-format/td-p/88899

TL;DR Create an M function that cleans up a sheet (promotes headers, etc.), and then add a custom column using the function, which you can then expand to get all your data combined.

Can provide additional guidance if you're still stuck on something.

[craigpowell]

Thanks for that Mark.

I've actually read that article but couldn't make it work as I missed one little detail which I have now noticed following your post.

Thanks