Power Query refresh deletes manually entered information

[morerockin]

Hello All,

Please see attached.

I am trying to edit my Power Query to allow me to manually enter data into the sheet and that data STAY with that corresponding row after refresh.

I've followed this video and I keep getting: "Expression.Error: A cyclic reference was encountered during evaluation."

Here are the steps I've taken. I get hung up at the 3:44 mark

https://youtu.be/duNYHfvP_8U

Please let me know how I can help.

[morerockin]

I'm kind of desperate!

[alansidman]

Can you provide the table for the second set of data. Difficult to reproduce your issue with out all data.

[morerockin]

Can you provide the table for the second set of data. Difficult to reproduce your issue with out all data.

Second set of data? Can you provide clarification? I am trying to add columns to the end of a query. I need the info in those columns to not disappear or get scrambled. Per the video, it gives guidance on how to do it.

Would the second set of data be the columns that I'm trying to add to the end of the query??

[MarvinP]

Hey morerockin,

I think your error is because you need two different tables to merge together. You need to build a second PQ (connections only) from your data and call this another table. Then when you import your new data from SQL (or wherever) it will merge the old data (with the comment) to the new data and write in the comment in the cell associated with the linked cell being used.

I'm certainly no expert but it looks like you don't have a second table you are trying to merge with and this is why you get your error message. Hope this helps. The Video was a little fast on some parts.

I'd create a second table on a new sheet just like the first and pretend that was the data coming in from SQL. Then I'd add a few comments in my original data and go from there to figure it out.

[morerockin]

Like the attached? I just created a 2nd PQ of the same set of data.

[alansidman]

No. You need to create a new table with the additional columns as they have done in the video. Upload that as the second table. Merge the two tables and only show the new data columns from the second table. I cannot see what you have done, as it appears that you are uploading data from a third party source and not from the file that you have attached and naturally I have no access to that third party location.

[CK76]

It's what's called self referencing query.

As others have explained. You'll first set up your query as normal. Then add 2nd query which will query the table that you are adding manual fields to (i.e. result of first query).

You may find below tutorial easier to follow.
https://exceleratorbi.com.au/self-re...s-power-query/

[MarvinP]

Hey,
I'm so close without getting all the way there... (darn)
See the attached to where I am.
afdafer PQ Merge Try 2.xlsx

You need to create two different tables (on separate sheets). I called one sheet "FakeSQL Data" the other sheet I named "You Edit"...
When you set the source for your two tables you need to set connection only as you can't write over an existing table (maybe?).

I believe both sources need to be TABLES and not sheets. Look in the Advanced View. This might be my bad and why it doesn't work yet.

I have both as "Connection Only" and wonder if that is wrong too?

Maybe one of the PQ Guru's will jump on this and fix it for us, and explain more about the YouTube link above that was skipped.

[MarvinP]

I think that CK76 pointed to the correct link. I go away and study that link now!!

[morerockin]

I think that CK76 pointed to the correct link. I go away and study that link now!!

Sorry MarvinP. I'm really dumb when it comes to this.

See attached.

In the Vendor Cost tab, that is the data my team provides to me with vendor information. It is found on Sharepoint. I suck this information in via Data Connection. The Source PQ tab is intended to replicate that. So for the sake of this sheet, "Source PQ" is nothing more than a data connection to the "Vendor Cost" tab.

I created a 2nd PQ called Price Sheet. This is the sheet that I need to add more columns too. This is a Data Connection to the Source PQ on the same sheet.

So, i believe I have 2 tables that I'm trying to merge, no?

See attached.

[MarvinP]

Hey,

CK76 above gave a link that better describes the intent and shows where the merge and comment selection is made. I don't have access to a SQL or SharePoint server so I'm just trying to make it work myself.

[morerockin]

Thank you CK76! This did the trick for me! SOLVED!

[CK76]

You are welcome and thanks for the rep