Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
craigpowell
Regular Visitor

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

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.

1 ACCEPTED SOLUTION
MarkLaf
Solution Sage
Solution Sage

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.

View solution in original post

2 REPLIES 2
MarkLaf
Solution Sage
Solution Sage

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.

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.