Forum Discussion
STOCKHISTORY FUNCTION
Thank you for your reply.
So according to your instructions I should do 2 separate queries which I later merge:
Source = Excel.CurrentWorkbook(){[Name="HISTPRICE"]}[Content],
Instead of adding all the columns I need from the sheet in just one query:
#"Filtered Rows2" = Table.SelectRows(Source, each ([Name] <> "BuySellTable1" and [Name] <> "PriceFeed1")),
As the book has various repeated sheets, I would have preferred to just have 1 query for each sheet. Is it possible without compromising the stable behaviour of the workbook?
Yes, that's possible, even if that's not very good practice. Please see OneQuery query in attached file.
I worked on the solutions you proposed, but the Table.NestedJoin(), removes the dates which does not coincide in the merged column. So I tried Table.Combine() instead, which seems to append instead of merging thus maintaining all the dates from both columns which is what I need, the problem I am encountering now, is with Column2 and Column3 as they expand and retract depending on if there is any data in the Stockhistory(). If I apply any steps to any of those columns, the query refresh returns error stating that those columns can`t be found, Can you suggest a way to include those columns dynamically, so that when there is data they appear and when there is no data they don not interrupt the refresh.
1) Previous file - we could replace errors on nothing and tables will be refreshed correctly
2) OFFSET - with an error in stockhistory query is not evaluated, thus table in excel sheet is not updated and you see what was returned on previous successful refresh
3) Duplications - you don't expand separate tables and merge them after that, but expand all tables and named ranges are in Excel at once, includes already returned by queries on previous refresh tables.
After that you expand all columns but DATE.1, CLOSE, VOLUME
If to rename DATE.1 to DATE it expands one more time.
In general such approach gives unpredictable results.
I have tested the different variations, when I use: =OFFSET(Sheet1!$R$2,0,0,(COUNT(STOCKHISTORY(Sheet1!$L$3,Sheet1!$L$4,Sheet1!$L$5,Sheet1!$L$6,0,0,1,5))/3),3)
for the named range the refresh works even when there is no data within the range, but when I use: =Sheet1!$X$2# the query refresh returns a Invalid cell value "#REF!" when the range is not populated. So I have decided to proceed with the Offset() formula. However there is another problem which occurs when I change the column name from DATE.1 to DATE, on each refresh the table duplicates the data, I have included both samples in the attachment, I would be grateful if you could have a look at the attachment and se if you can figure out why this happens.