I cannot use a power query parameter to refer to a data source in the Mac version of Excel. Is it broken?

Question

I have tried various methods to make a power query data source dynamic, including creating a new parameter called "Path" and referencing (Path &"filename") as the data source, naming a cell in worksheet to dynamically point to the current path & file name, etc. etc.

All of these attempts have worked when opened with Windows, but fail with Mac. Various error messages are generated, depending on the approach including "on premises gateway is required," or "not a valid absolute reference," etc.

Am I wasting my time? Does the current version of PQ on Excel for Mac support ONLY absolute references to a data source?

Answer 1

Hi @James R. Sick,

Currently, Power Query on Excel for Mac only supports absolute references to a data source. Dynamic references or parameterized data sources are not supported on Excel for Mac. If you are trying to create a parameter to refer to a connection string, you will need to create a separate connection for each value of the parameter.

You may encounter some error messages such as "on-premises gateway is required" or "not a valid absolute reference" on Excel for Mac when you try to use features that are not supported. This is because some Power Query functions require certain features that are not available on Mac, such as On-premises data gateway for Windows Authentication.

Also, I would suggest going to this Microsoft feedback portal for Excel: https://feedbackportal.microsoft.com/feedback/forum/c23f3b77-f01b-ec11-b6e7-0022481f8472 and raise a thread there. The product team actively monitors feedback there and will make improvements in the future.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.