Pivot Tables Created with the Query Function

Question

I created a Master and then separate Pivot worksheets using the query function in excel.  However, when I send the file to a colleague, the file will not refresh because it is looking for my workbook, even though the table was created using the current workbook.  How can I fix this?

Answer 1

Sorry, I don't know where to look.

.

MS has a long tradition of "hiding" stuff.  I have a spreadsheet I use daily. It shows no Queries and no Connections, but still insists on asking every time if I want to update "something" when I open it.

Answer 2

Thanks for the references.  They all refer to the Source data, which in their examples was located at c:\user\files\Spreadsheet.xlsx.  My source data says it is coming from Excel.CurrentWorkbook, which is what I want.  So I don't understand why when I send the file to someone else, it is looking back to the file on my c:\ drive.

Answer 3

Here is the link to the file:

https://1drv.ms/x/s!AqTM-eBd\_Jy\_ghnz7AEMG-ashuNb?e=usVNvc

The password is Data2020

Answer 4

Excel is being "helpful" and adding the full path ... "somewhere" ... in the query.

This looks like a good possibility of a fix

Create a relative file path in Power Query     2019 08 06

https://excel.tv/how-to-create-a-relative-file-path-in-power-query/

https://www.youtube.com/watch?v=DhRjtW8mrQs&feature=youtu.be (6min)

In this blog post, we'll talk about how to create a relative file path. I'm sharing this because it's a common problem: the client asks me to build something, which happens on my computer, but then I need to send it to them. If the file path is absolute, as you'll see in this example, the client won't be able to correctly run the query.

.

The instructions to create a relative path are as follows.

.

Although it is talking about OneDrive, SharePoint is functionally equivalent. Take a look at the suggestion in this tip:

@ OneDrive- Get Data From OneDrive Excel File Using Power Query          2020 02 16****https://excelexciting.com/get-data-from-onedrive-excel-file-using-power-query/

We will see how can we use Power Query (Get & Transform Data) to connect the Excel File from OneDrive and refresh it. Currently, Power Query hard code the file path in the source step when we get data from file/folder if the OneDrive is setup on your PC.

If you share this workbook with another user and when they refresh Power Query they will be prompted with an error due to location not found.

.

MSPTDA 20: Query Parameter for Dynamic Folder Location / Data Source in Power BI & Excel Power Query2019 04 28

https://www.youtube.com/watch?v=0NX-GctfZuU       11min06

In this video learn about how to create a variable or parameter for a data source path (folder path in this video) in Excel Power Query and in Power BI Desktop Power Query. Learn about Query References other queries or steps, so it may not directly access a data source Error in Power BI.

1. (00:23) Introduction
2. (02:36) Excel Power Query Example of Parameter for Data Source / Folder Path
3. (08:48) Power BI Example of Parameter for Data Source / Folder Path
4. (10:23) Summary

.

Here are some other options

Data Source and Step Level Settings in Power BI / Power Query: aka The Gear Icons  2019 05 21

https://www.poweredsolutions.co/2019/05/21/data-source-and-step-level-settings-in-power-bi-power-query-aka-the-gear-icons/

I recently had a really heated conversation with a very close friend of mine who I can only call as an expert on Power Query.

It was a pretty heated conversation because this person was on the “dark side”, as he didn’t know of what the Data Source Settings button actually did and was using dark [M]agic to make things work.

This was pretty crazy to me! I use this button / feature almost on a daily basis and I can’t stop recommending it to people who use Power BI / Power Query, so why would this person choose any other method over this?

Let me show you WHY this is such a big deal and why you need to start using the gear icons more frequently.

.  *  changing Folder Paths for the Combine Files Experience

.  *  Option 1: Using the Source Step gear icon (The Dark Side)

.  *  Option 2: Using the Data Source Settings (The Light Side)

.

Consolidate Multiple Excel Workbooks in Power Query****2019 03 01

https://yodalearning.com/tutorials/consolidate-multiple-excel-workbooks-power-query/

The limitation of Excel of consolidating multiple Excel workbooks into one has overcome by Power Query.  Power Query can do this consolidation task in a couple of minutes. This is explained below within a few easy steps. Consolidate Multiple Excel Workbooks By Power Query

.  Step 1: Open Excel Workbook from Folder

.  Step 2: Add Folder Path

.  Step 3: Remove the Unwanted Columns

.

.

Right at the end of this video it shows how to change the file path in Queries

Import Multiple Excel Files & Combine (Append) into Proper Data Set (Workbooks) MSPTDA 04         2018 07 04

https://www.youtube.com/watch?v=rSQwZ1d3b1g (32:03)

Import Excel Workbooks from folder

Extract Filename to create new column for City

Extract Sheet name to create new column for Sales Rep

.

In this Video learn how to import data from multiple Excel Workbook Files and append into a single Proper data Set. Topics:

29:35 Edit Query when Folder Path Changes

30:57 Summary

.

Answer 5

It is obvious that you have use the full pathname of your file... for what reason ever.

The easiest way is to show us your file. You can delete the confidential data or use random values, that doesn't matter.

Share OneDrive files and folders - Office Support

Andreas.