Hi Tracy,

I couldn't find any queries called "UniqueStations". You also said 'I amended the media owner', what do you mean by amended? What exactly did you do...change the column name, filter rows, replace text?

I also don't know what you mean by "Station called Home Channel should be linked to OTHER, was originally linked to MEDIAMARK", did you use this in a join? What do you mean by 'linked'?

I suspect you have applied a filter along the way that is causing the problem and because I don't have access to your source files I can't see any of your data in the query, only the M code. Troubleshoot by going back through the steps to see if at any point the data is correct. Then you can focus in on the step causing the problem.

I hope that helps point you in the right direction.

Mynda

Hi Mynda,

Apologies, the comments do sound cryptic when you don't have s/sheet. There are supposed to be two workbooks, one was attached, the other (ZenithMP_CleanedNew) was a link to OneDrive as this file was too big to attach and this one contains UniqueStations query.   https://1drv.ms/x/s!Ao6UIfBSUm6lliqrTFXwS6Gyks9S

By amending the media owner, I mean that in my original table data in UniqueStations, I replaced "MediaMark" with "Other" in the media owner column.  In reference to the "Home Channel".  No links, this is just a dimension table to merge with the other queries to get a media owner column as one would use a vlookup.

In the meantime I will try looking for a filter.  I have been through the steps a number of times and can't find anything, but I will try again.

tnx

Tracy.

Hi Tracy,

Thanks for sharing the second file.

I can't see a 'Replace Value' step in any of your queries where you replace "MediaMark" with "Other". There is only one 'Replace Value' step in the 'ZenithMP_cleanedNew' query inside the 'ZenithMP Cleaned New.xlsx' workbook, and it's replacing blanks in the Rate Card Cost.2 column with 2018.

So, I'm either missing another file, or the change you thought you'd made hasn't been retained in the query.

Mynda

Hi Mynda,

It's not easy to explain in email.  

I didn't make the change in the UniqueStations query, I made the change in the data table in another excel sheet. On refreshing, the UniqueStations query updated with the correct media owner and the changes reflected in the all the append queries except for ZenithMP- this still shows that the station called Home Channel is owned by media owners Mediamark when it should have changed to Other, as is the case with the other queries.  So I don't understand why the other append queries were refreshed with the changes and not this one.  I also don't how to make this change in the ZenithMP query as I can't simply replace MediaMark with Other since it must be changed in relation to the Home Channel only.  should I try an recreate the query from scratch?

tnx

Tracy.

PS.  If you open the ZenithMP Workbook and open the query ZenithMPforAppend and click on Media Owner column, you will see that there is no Mediamark which is correct.  However, when you open the PublicisAllClients workbook and open the ZenithMPforAppend query and click on the media owner column you will see Mediamark here.  Why is this one not being refreshed?

tnx

t

Hi Tracy,

Thanks for filling in the blanks. I understand now.

For Power Query to get updates the workbook containing the source data must be closed. i.e.  C:TKANALYTICSPUBLICIS MEDIACHASE FILESUniqueStations.xlsx must be closed to refresh the queries in the ZenithMP Cleaned New.xlsx file.

And to refresh the Publicis queries both UniqueStations.xlsx and the ZenithMP Cleaned New.xlsx files must be closed.

It's also not a good idea to use the Query loaded into a table in the current workbook as your source. You should refer to the query directly, as opposed to using Excel.CurrentWorkbook to reference the Table in the file. The reason being; the first refresh will refresh the table loaded to the worksheet, but it won't refresh any queries referencing that table because they will refresh before the table has time to update.

If closing the workbooks doesn't fix the issue, and you've Refreshed All multiple times, then I recommend rebuilding your queries so they don't reference Tables that are also queries themselves.

Mynda

Thank you so much, the query miraculously updated, probably because as you suggested, I refreshed with the source workbook closed. I take on board the advice re not using query table as source.

tnx

Tracy