Forum Discussion
Name Not Updating in Power Query When Overwriting Source File
I'm trying to rerun my Power Query after updating a source file but whenever I run it, I get this error:
Steps to update source file:
- Downloaded an updated source file from an internal dashboard to my downloads folder. The file type is CSV and the file name is saved as ContentAuthorReport ([Version #]).csv.
- Opened CSV and saved as XLSX file type, overwriting the pre-existing ContentAuthorReport.xlsx file in my Source Documents folder.
I looked in the Source step and see it has the Name and Item columns set to the ContentAuthorReport (3).
When I try to create a new query with the updated file just to see what comes up, ContentAuthorReport (4) appears.
I've checked the Info tab on the source XLSX document and the properties dialogue box but I can't figure out why the version number is still attached to it or how to remove it.
I attempted to edit the code to remove the (3) but that didn't do anything. Unedited code below:
Source = Excel.Workbook(File.Contents("\Source Documents\ContentAuthorReport.xlsx"), null, true),
#"ContentAuthorReport (3)_Sheet" = Source{[Item="ContentAuthorReport (3)",Kind="Sheet"]}[Data],
I would like to be able to update this by just saving over the old source file so any help is much appreciated.
- In the step where you save the csv as xlsx, what is the name of the worksheet? Make sure it matches what is in your query.
- In the step where you save the csv as xlsx, what is the name of the worksheet? Make sure it matches what is in your query.
- Thank you! I don't know why I didn't think to check the sheet name. I appreciate your help!
There is also a way to set up the query so the tab name doesn't matter. When you choose Data, Get Data, From Workbook, a window opens showing some details about the workbook. Make sure you click the main branch of the tree that is displayed:
then click "Transform data". Power Query will show the elements of the workbook in a table, take these steps:
- Filter the "Kind" column for "Sheet"
- Filter the Name column for some text filter like "Begins with"
- To make sure we're only importing one sheet, click "Keep rows", "Keep Top Rows" and choose to keep only 1 row
- Consider if these steps will always give you the sheet you need. If not, change the above steps, these are just examples!
- Click the tiny button with the left and right pointing arrows next to the Data heading in the table.
