Power Query for Excel Enable load
Hello all, Does PowerQuery for Excel has the option to stop loading some queries? I Collect data from multiple sources and then append them to one query, then when I load the data all the queries and tables are loaded, the original and the appended tables, this results in data duplication in reports. I found an option in Power BI to enable or disable loading data for selected queries in the same file as shown in the following screenshot: Where can I find this option in Power Query for Excel or how can I solve this problem when I append multiple queries in Excel? Thanks
Unable to Connect to Web Source With Credentials in Power Query
I have tried to connect to 20+ websites that require sign-in credentials using Power Query without success. I am able to login using Chrome so the credentials are correct. The error I receive is below. I have clicked Edit Credentials and re-entered the creds, only to get the same error. I have cleared the permissions in Power Query and re-done numerous times without success. Also, I'm certain I have used the feature successfully around a year ago. Is there a setting in Power Query that enables connecting with credentials? Any other ideas?"The input couldn't be recognized as a valid Excel document."
Such alert could appear when you try to use Power BI connector on Excel file. It's understandable if the source file is corrupted and can't be opened in Excel. However, it looks strange if Excel opens the file in question and shows nothing wrong. Based on our experience above is usually means what something is wrong with XML scheme of the Excel workbook. Mushup trace (Data->New Query->Query Options->Diagnostics->Enable tracing) could give some additional information, but often not enough to find the reason. We had two main scenarios XML scheme is not complete Usually if Excel file was generated by third-party tool. Such tool could generate quite limited XML scheme which is enough to open the file in Excel and to work with it, but not enough for Power BI connector. As an example, trace log shows [DataFormat.Error] The input couldn't be recognized as a valid Excel document.\r\nStackTrace:\n… … [DataFormat.Error] We couldn't find a part named '/xl/sharedStrings.xml' in the Excel package.\r\nStackTrace:\n… Such case is easy to fix – it's enough to open the file in Excel and save it (without any changes) – Excel is clever enough to fix the scheme. For the routine regular tasks we use poweshell script which does exactly the same in background. There is the link within Excel file which is not recognizable as valid Usually if Excel file is synced/kept with some cloud storage. One of the variants, wrong link could appear with copy/paste from another such file. That could be active link in one of the cells; or the link within conditional formatting formula; or even the link which actually isn't used by Excel but kept somewhere inside the scheme. For example, in one of the files I found in Data->Consolidate->All references the link like '\\drive.tresorit.com@7235\Tresors….\[file.xlsx]Sheet'!$AC$6:$AC$357 on the file which was deleted long ago and isn't used, but for some strange reason the link was kept within the scheme. Unfortunately for such case trace log doesn't give enough information to localize the issue, it looks like [DataFormat.Error] The input couldn't be recognized as a valid Excel document.\r\nStackTrace:\n… … nExceptionType: System.UriFormatException, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089\r\nMessage: Invalid URI: The hostname could not be parsed.\r\nStackTrace:\n Perhaps I have not enough knowledge for more straight forward localization of the problem, but the only way is to exclude Excel file parts one by one and check if the issue disappeared. Another way could be to unzip Excel file and check if wookbook.xml or sheetNN.xml have something suspicious inside.
Problem : Power QUery will notload to Data Model
Background: A PowerQuery had been successfully combining data from a dozen large (101 cols x 100,000 row csv files. The table was extensively cut with ~80% rows filtered out , unwanted cvolumns removed, and some new data columns added. Output was a table loaded to workbook only. This ran successfully over several months with a new CSV data file being added to the list every week. The PQ got somewhat slower, as the data got more extensive, but still ran fine. Problem : on trying to develop this, I wished to be able to load the table to the Workbook Data Model, in order to add some measures, based on ISO Week calendar (e.g. show variationof latest week against average of preceding weeks). Result : if the Query settings are changed to 'Load to Data Model' (irrespective of load to Table, or Connection-only) then the query goes into a spinning-wheel-of-doom. The query shows 'Retrieving Data...' but the row-count never updates nor does it show any source files being loaded. The only way to stop it is to hit ESC, in fact sometimes that doesn't work either and I have to start Task Manager and aim to kill the Excel process; it isn't needed to actually kill it, just to issue the Stop Process command seems to eventually interrupt the loop. Effect : this means I am restricted to loading the combined data into a Table and cannot add context-specific measures. More generally : I am finding Get & Transform powerful for simple use-cases, but in this example, no alone have I found this 'does not load to Data Model' issue but I'm also suffering from a lack of knowleddge and resources on how to optimise the data-load. I am sure there is some ability to cache the data from the older CSVs (only 1 new one added each week), but it is impossible to fnd any decent resources.
Simple List of Combinations From Single Row Table
Note: Novice User, using Excel 2013. I want to create a non-repeating list of possible combinations/permutations from a single column table. I want to make a simple table called "letters". The number of items will vary. I'm using alphabet characters in the example but imagine these could be easily apples, oranges, salad, drink....and so on. In a separate tab I want to generate a list of non-repeating combinations, order does not matter, and I want to run this list in varying string length (1x, 2x, 3x, 4x, ...) until final result with all items combined. Sample output I imagine would be: A B C D AB AC AD BC BD CD ABC ABD BCD CDA ABCD If possible from formatting perspective, I would like to display the combinations separated by "+" character. Any help would be appreciated.
Excel: Auto-populate outputs based on input change in a table
I have a very complex financial model made up whose output changes based on the 2 inputs. I want to make a table where the column has all possible values of input 1 and row has all possible value of input 2. Each cell in the table should have the output corresponding to the two inputs. How do I go about this? Can you provide a link or a function that I can use to do this?Data analysis function not working
Good afternoon, I am using Excel 2013. I have added in the Data Analysis add-on (and the VBA one). The data analysis button shows up on the right of my toolbar, but when I click it nothing happens..the box with the options of which test to run does not pop up anywhere (and I've looked to see if it's hidden behind another pane, etc.). I've tried un-installing the add on and re-installing it, Restarting my computer, etc...nothing has worked thus far. Any other suggestions?
