With Query Editor, you can navigate, define, and perform data transform operations over a data source. To view the Query Editor without loading or editing an existing workbook query:įrom the Get External Data section in the Power Query ribbon tab, select From Other Sources > Blank Query. The following video shows the Query Editor window appearing after editing a query from an Excel workbook. The Query Editor only appears when you load, edit, or create a new query. To learn more, see Get & Transform in Excel 2016. Information provided here applies to both. I do this anyway sometimes to speed up the Excel table refresh.Note: Power Query is known as Get & Transform in Excel 2016. Query, then link Excel to that local table. If the root cause isn't resolved, short of creating your own macro for populating the Excel table, the easiest and most reliable solution is to create a local table within Access based upon the problem Use of a UDF within the query can also foul things up. The underlying issue is often the presence of a linked table somewhere in the nested query structure. It is an elegant revenge because this makes the root causeĪll that much harder for us to track down :) Revenge on the part of the wizard because it has to work harder. Most commonly this occurs when I have a complex, nested set of queries: I've always suspected that this is simply Ie Regsales 1000 + Whatever 500 + Other 500 = 2000 in the query, and when I sum the three columns in Excel.īut when linked in Excel the sums are not correct the above example's Net Income would be 1544.07, but other rows sum properly.Ĭan you post this database somewhere I could download it, or else e-mail it to me (in compacted and zipped form) at the address you can get from my website? If possible, post/send a cut-down version that includes only the elements necessary to demonstrateĪccess tips: There are a variety of reasons why the ODBC wizard cannot find certain queries. HOWEVER - even though in the query the sum of these columns is correct, when linked to Excel *some* of the rows' Net Income is incorrect, by variances I can't ascertain. That did it, the query shows up in Excel's Select Table list. Instead of doing + + in the query, is used Sum( + + ).
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
September 2023
Categories |