Forum Discussion
Solved
Refresh Data join with extra Excel columns
I have a table that is linked to my database table. I've added some extra related columns in my worksheet. When I click Refresh Data the added columns become out of sync.
Any thoughts?
Wasn't getting any traction over at StackOverflow
That significantly complicates the task. It could be like:
1) query source table
2) Reference source table, rename as maxValue
3) select in it Value column, Transform->Statistics->Maximum
4) Reference source table, rename reference as Range
5) Add Column -> Custom Column -> name as max -> =maxValue
6) Select Value column -> Add Column -> Standard -> Percentage Of -> from drop-down near Value select Use Value or column -> select max
7) Select Percent Of -> Transform -> Standard -> Divide -> 100
😎 Transform -> Data Type -> Percentage
9) Add Column -> Conditional Column and here combine something like
10) Select columns ID, Value, Range
11) Home -> Remove Columns -> Remove Other Columns (from drop-down menu)
12) Home -> Close & Load To -> Table -> select desired options
7 Replies
- @Detif Lewin, I did the example that's in the link and it works great! Thanks again. However, I noticed if I put a hyperlink in the extra columns and do a Data Refresh the text stays but the hyperlink goes away? Any thoughts?
It's the same in the Excel grid. If you do a lookup only the value is returned not the hyperlink.
I setup the self-referencing table and it worked great. I tried to add another column next to notes column that contains a formula but every time I refresh the data the formula is replaced with just value of the formula. Is there not a way to have formula persist during a refresh data?
I believe that is the culprit with self-referencing tables. You are joining the table with itself and at that point any formula you created on the Excel side is converted to its value.
Try to create the formula on the PQ side.
