Combine Worksheets in a Workbook

Power BI
1

Hi
I am a Newbie to PQ

I have, for another project, successfully combined files within a folder where all the files have the same structure.

I now want to combine all sheets in a workbook - the sheets do not contain excel tables but all have the same structure (that is the same column headers in the same columns).

How do i combine the sheets into one table.

Thank You

2

@AllisterB,

Append should do the trick here. Check out this video:

This entire course is an outstanding overview of data modeling and Power Query. Highly recommend everyone starting out with Power BI take this ASAP - provides critical foundational skills that will make everything else much easier.

  • Brian
3

Hi @AllisterB,

I fully agree with Brian, if you’re just starting out with Power BI make going through the Advanced Transformations module a top priority.

The structured format of Excel tables make getting the data in Power Query easier and prevent data loss, so always consider this.

Here’s an article, I think you will find helpful. It addresses your question and shows how to solve a potential issue that might arise…

1 Like
4

Thank you Brian Melissa.

The Article from Trump Excel relates to PQ. I’ve tried to do teh same in Power BI and the Ribbons and functionality of PQ in Excel and the functionality of PowerBi seem different enough - is that correct and if so how do you decide whether to use PQ in Excel or Power BI?

Thanks again

5

Hi Allister,

Yeah there are some differences between PQ versions of Excel, Power BI and/or PQ Online. Since your goal will be to analyze it in Power BI - I’d go for that.

If you can post a small sample file, I’d be happy to take look.

6

@AllisterB,

Totally agree with @Melissa - go with Power BI. I’ve used Excel extensively for 30+ years and would consider myself expert at it. However, since learning Power BI, I barely touch Excel anymore, except as a way to import data into Power BI.

In addition, the best online training resources all use PBI as the primary platform. Makes it easier to follow if you’re comfortable with PBI Desktop.

  • Brian
7

Hi AllisterB.

You can check this one out. I am sure it will solve your problem.

8

To the contributors of this post, thank you for all your inputs on this topic we are now tagging it as Solved. To help us learn more about your experience in the forum, please take a moment to answer this short forum survey. We appreciate all your help and suggestions. Thanks!

9

Hi there,

You can use the append feature in PQ and transform the data into one table.

Thanks

10

Hi @msaxena, If you have a follow question or concern related to this topic you may reopen a new thread. You can check the forum guidelines here - How To Use The Enterprise DNA Support Forum. Thanks!