How to combine the rows in multiple worksheets into one master sheet?

[LegalSculptor]

Apologies if the solution is trivially obvious but for the life of me I can't find the solution. Here is the problem:

I have two worksheets functioning as a document log of sorts. Each worksheet represents a particular category of documents.  They both have identical fields. I would like to have a third worksheet that serves as a master. This third worksheet would be populated by any additions made to the other two worksheets. It would also automatically propagate any edits/changes made to the corresponding row in the other worksheets. 

Is there a way to automate master worksheet population without using macros? As it is right now, I have had to manually copy and paste rows into the master worksheet and ensure that any edits were also copied over. I prefer to have each of the two worksheets serve as the single source of truth for the data in the master worksheet. 

System Specifications:

Microsoft Excel for Mac

Version 16.63.1 (22071301)

MacOS Ventura 13.0 (Beta 5)

PS
In various support forums and videos, I see others have multiple options when using Power Query. On my system I see only two: Excel Workbook or Text/CSV. None of the myriads of options I see on support forums. Is this a limitation that exists only on Mac versions?

[#2]

@LegalSculptor First of all, Excel for Mac doesn't yet support all PQ functionality you may have read about. That said, why would you maintain two separate sheets, wanting to create a third as a master? Put all in one sheet, add a column for the category and work with that. 

[#3]

@Riny_van_Eekelen That certainly makes sense. But my employer prefers that it is presented in this manner (simply pivoting on one worksheet is not desirable.) 🤷🏽‍:male_sign:

[#4]

@LegalSculptor Perhaps convince your employer that he/she is wrong.

[#5]

Unrestricted fully up to date Office 365 for Mac subscribers now have full PowerQuery capabilities.

[#6]

@JimGMac Which "unrestricted" version would that be. I'm on 16.64 (22073100). It certainly has a great deal of PQ but I wouldn't call it "full capabilities". Am I missing out on something?

[#7]

@Riny_van_Eekelen 

I think all the functionality, including DAX, are supported in Mac Excel PowerQuery. Are you aware of something missing?

[#8]

@JimGMac 

You "think all the functionality, including DAX, are supported in Mac Excel PowerQuery". And yes, I am  aware of "something missing" and surprised that you are not.

But let me begin by saying I'm extremely happy that PQ finally came to the Mac not too long ago. Though, it is still for Insiders Beta Channel only, as far as I know.

To mention just a few differences, you can't connect to a web-site or all files in a folder. You can't directly connect to a table or range in the current workbook.  You can't choose to have a connection only query and change it later. All queries load to an Excel table (by default) which you can then remove, of course. But you need to go back to the query editor, duplicate the query and close and load again, if you change your mind. Another major feature that is missing, though I trust it will be coming soon, is IntelliSense. In the PC version, PQ helps you complete M-code as you start writing a piece of code. Suggesting functions to use with instructions on how to use them. An invaluable tool, in my opinion. Without it, writing M-code from scratch is extremely challenging. Especially, for Mac users who are totally new to PQ.

And then, when it comes to DAX, that's part of PowerPivot / Data Model, which is not supported by Excel for the Mac (yet).

[#9]

@Riny_van_Eekelen 

Hi Riny,

You're right that I have very limited PQ experience, as it has not been on the Mac until recently.

Please excuse my misinterpretation of DAX, as I thought that is what the PQ editor was writing. I really have to get my manuals on PowerQuery out and go through them now. I didn't know DAX is restricted to the Data Model, which is not supported and probably never will be.

You CAN directly query tables in the active workbook. That I have tried and it works. Use the Excel connector and choose the file you currently have open.

The Advanced editor seems to have Intellisense. I haven't used the Windows Excel PQ to offer a comparison.

[#10]

This is not helpful. I'm in a similar situation as OP. We have a project document with separate tabs for each project manager, but we want to see a consolidated list of all active projects. I'm trying to pull the data from each tab into a single worksheet but I can't find a simple solution. The column headings are all the same.
Using a single worksheet isn't practical since each project manager edits/manages their projects individually. But we do need an overarching status of each project.

[#11]

@PoKaroo Are you also using a Mac?

[#12]

@Riny_van_Eekelen Yes on mac. Version 16.64

[#13]

@PoKaroo I'm sorry for not being very helpful here. If you are not an Insider in the Beta channel, you'll have to wait until PQ comes to the Mac for the general public (don't know when that will be), get your hands on a PC and use PQ on that one. Otherwise you are going to be stuck with manual copying and pasting or developing a macro that can do it all for you. Unfortunately, VBA and macro's are not subjects I fancy getting involved in.

[#14]

@Riny_van_Eekelen and VBA and Macros often times are prohibited by corporate IT security policy (for better or for worse). 

Is it possible to sign up for Beta access? Or does one have to be a developer in order to do so? 

While I understand that using pivot tables would 'essentially' produce this result, I am somewhat surprised that Excel, in its entire history?, doesn't have a function that simply consolidate rows in the various worksheets (with these worksheets serving as the single source of truth) into one. It seems a rather basic feature. 

Nevertheless, I have accepted that, for the time being, this simply is not possible to do on the Mac without the use of Macros/VBA (or PQ on PC). 

[#15]

@LegalSculptor More about the Mac insider program in the link below:

https://answers.microsoft.com/en-us/officeinsider/forum/all/welcome-to-the-office-insider-for-mac-co... 

[#16]

https://insider.office.com/en-us/join/mac (for office 365). Unfortunately, their directions are incorrect as there is no such menu option "Check for update" under Help in any of the applications. Thanks for the assist in any event.

[#17]

Managed installations often don't allow individual clients to install updates. They are managed by the IT department.
Testing of the Mac PowerQuery interface is continuing. I haven't seen anything negative so far, which is a good sign that releasing it to the world is not too far down the road.
When PowerQuery is rolled out to everyone, it will roll out in phases. Whether you get it or not will depend on your license type and to chance, as Microsoft rolls out major new features to groups of users, not everyone at the same time.