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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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