User Profile
heylookitsme
Brass Contributor
Joined 6 years ago
User Widgets
Recent Discussions
Re: Power Query INDEX Column
Your response prompted me to re-check my Table and I found that Rows 6208-8300 were not formatted as part of the Table. So, I resized the table so it would capture those as well. Unfortunately, I just assumed my Table included all items. Rookie mistake on my part. Thanks for the swift response.24Views1like0CommentsPower Query INDEX Column
I added a column for Index using Power Queries built in Index Column function and I placed it as the last item in steps. When I run the query there are 8,300 rows in my table however, the INDEX column populates numbers 1-6207 and then the rest are blank. Maybe I am not using the right method for what I am needing to do. I am just needing a column at the end of the query to number the rows. Further up in the steps before the INDEX Column step takes place, I do have a filter that takes place based on a cell's value that is passed to a table. I wouldn't think that would prevent the numbers from populating all the way down but who knows? Anyone have any ideas on how to make this work or perhaps a better way to go about this?Solved71Views0likes2CommentsPower Query by Default Excludes First Blank Column
I have researched this down the rabbit hole long enough without any answers. In Excel I created a Power Query Get Data from Sharepoint Folders using the Contents method. There are about 10 files where the data is in the same format, columns match sheets are all the same name, etc.... FYI, None of them are setup as Tables for reasons I won't go into. They just can't. When I import the files 5 of the files have data in column A while 5 do not. When I import the data the files that do not have data in column A Power Query is automatically removing those columns from those sheets. Which then screws up the column order when the data is appended and loaded to the table. How can I force Power Query to bring in Column A even if some files column A are blank?Solved132Views0likes4CommentsPower Query Find Text and Delete Anything After
Excel Power Query what would be the M Code for finding the String "NUL" and then remove any Text after the "NUL" String. I have several serial numbers for example like 1287JLPO-NUL-00-000-00000 HLQA-00-NUL-000-0000 I just need to delete any text after NUL. As you can see, I can't just replace - or 0 because those could be found prior to NUL. Thanks in advanced for your help and I will be sure to mark your answer when complete.Solved919Views0likes4CommentsRe: Slow Excel Sheet - Even after everything's deleted
Solved my problem. I'll post in case it helps another user out there. Check to ensure you do not have too many objects, comments or shapes in your sheets. This can be caused by copy and paste procedures inadvertently pasting them many times over without you realizing it. Simply, select the Home option in Excel ribbon and use the drop down under Find and Select. Select selection panel and you will see to the right if you have a lot of items showing that need to be deleted.2.5KViews0likes1CommentSlow Excel Sheet - Even after everything's deleted
Okay, this one blows my mind. I have a sheet with a table that acts like it contains volatile formulas. It's very laggy, filtering is super slow, manual inputs are slow. Even after I have deleted everything on the sheet!!! All the things I tried below are after I deleted all data in the sheet. File size is only 8MB. First thing I checked was VBA and it does have a macro that runs a worksheet change event. This was all commented out and still runs like this. I checked Formula Names to see if there were any external connected workbooks, and there was not. I checked Queries & Connections and there are none. One strange thing I noticed is when I select any other sheets in the workbook it selects the sheet instantly and I can work quickly or as normal in the sheet without any hesitation or lag. When I go back to the problem sheet and click on it, it is slow to react. Any cell within that sheet that I select (non edit mode or edit mode) it lags behind about 2-3 seconds before selecting it. I do not have any other workbooks open and little to no other programs open. I have a laptop with high specs that could run circles around most laptops. I don't know what else to check that would potentially fix this issue. Re-building this workbook will take way too long, potentially days. Oh, I use MS 365 Enterprise 4 with the latest updates and I am on Current Channel using Win 10.Solved2.1KViews0likes2CommentsExcel Memory & Performance Limitation
My previous laptop (which had some great specs) I noticed that when Excel Memory Usage rose above 1,800 MB that it would start behaving badly and do really random weird stuff (Example: Randomly freeze for short periods of time, click in a cell and it does not recognize that you clicked on it, but the data is in the formula bar, and you can change it. These are just two off the top of my head, there are many other random things as well). Now I have a new laptop with amazing specs, and I am using Microsoft Office 365 Enterprise 4 64-Bit version with the latest updates installed. I am still noticing that Excel is having issues when the Excel program memory goes above 1,800 MB. What is the deal? Does Excel have a limit on how much memory it uses? I did check my options and I'm using all threads with no limits listed. Is there a way to increase this limitation, if in fact one does exist? My Lap specs are; AMD Ryzen 7 PRO 7840U 8 Cores, 16 Threads Ram Memory 32 GB Microsoft Win 10 Pro Lenovo ThinkPad T16 Gen 217KViews0likes1CommentRe: Latest Version File Save Errors when File is in .xlsb format
Thanks for the advice as it hope will help others. As for me I had already did steps 1-3 previous to this post. However, how do I report the issue directly to Microsoft? As for work-a-rounds I mentioned one in my post and have been changing what files I can from .xlsb to .xlsm. However, most of my files require they be in .xlsb format. Makes for easier transfer of macro enabled files and to reduce file sizes.2.5KViews0likes0CommentsLatest Version File Save Errors when File is in .xlsb format
I am using MS Office 365 E4 Version 2401 (Build 17231.20236) Current Channel. Ever since I received this latest update I am having issues saving .xlsb files. I can open them but then I get the unable to save workbook is corrupted, blah, blah, blah. To fix the issue I have to copy my work to another workbook (so I don't lose it). Close the file with error out without saving. Then open the file back up under Open and Repair. The file is repaired and works just fine again. Not sure if this is an issue the MS team has identified and are working on in the next revision, but I hope it is because it is really annoying.Solved2.8KViews0likes2CommentsRe: Excel VBA to import data from Excel to Sharepoint Approval
It is looking that way. I have setup several PA processes so I am pretty familiar with it. Do you or anyone here know how to create a new approval but not submit it? Why you ask, because I want it to populate some data into the approval form automatically and leave it open and in view for the user to review, add the approvers and then the user selects submit. The approval form has a lot of data entries that the user has already done in an Excel sheet. I am preventing them from having to enter the data twice.2.2KViews0likes3CommentsExcel VBA to import data from Excel to Sharepoint Approval
Getting ready to start a project where I need to upload data from an Excel sheet into a Sharepoint site that has a custom Approval Form. I have a few ideas on how I might do it but with Excel, Sharepoint and Approvals App changing what seems to be on a daily basis. I would like to hear a few ideas from any one else who might have the time to share. The user will enter data into an Excel workbook and upon pressing the Macro Button named, Save & Submit, it will open the sharepoint site page, create a new approval using the custom approval form. I want it to populate matching data from the Excel file into the Approval request and then show the approval form for the user to review before sending. The user will be responsible of selecting the approvers that data will not come from Excel. I have never used VBA to open a Sharepoint Site page, input matching data, and leave form open before. Not even sure if this is even an option. As for matching input data it is as simple as Excel has a Cell that always holds First Name, Last Name, Category, Effective Date, Etc... Which match the fields in the Sharepoint Approval. The ideas I have for doing this, I feel are more complicated then I think it may need to be. Which is why I humbly ask for any input any one may have time to share. Oh, I am using MS 365 Enterprise version.2.4KViews0likes5CommentsPower Query Very Slow using get files from folder
I have a Master file that uses power query to import all xlsx files from a folder. Pretty simple setup, since all the files are setup (have the same headers 55 columns) and formatted the same way. Currently the folder that holds the files is up to 103 files at 145MB. When I open the Master file and select refresh all it takes up to five minutes for it to update. This is way too long. I used power query because I figured it would be the fastest way to update the master file as new files/data is added. Any suggestions on making it faster? I have already selected fast data mode, and data options within power query to make it as fast as I could. I have recently received the compatibility warning when I try and edit the query Queries in this workbook might be incompatible with your current version of Excel. Queries were authored with a newer version of Excel or Power Query and might not work in your current version. PQ version is 2.96.842.0 My PC specs easily exceed the requirements to refresh this data. I am using 365 Enterprise 3.9.9KViews1like1CommentExcel's xlookup formula not working in latest Build Version
I have a few folks using MS 365 E3 with the latest version 2202 (Build 14931.20120) Current Channel and when I use the xlookup formula it shows it as .xlfn in the formula bar when they open it up. That means that their version of Excel does not support that formula. IT has assured me they have the latest version of Excel so I am at a loss of what to do. I am using Version 2108 (Build 14326.20784) Semi-Annual Enterprise Channel. Anyone have any ideas on what I need to do? IT Could be wrong and they don't have the latest?761Views0likes0CommentsTo Power Query or Not to Power Query that is the Question
I have a table that has 140,000 rows with data and that is only 6 month's worth so it could potentially go up to 300,000 or more records by the end of the year. Originally I set up my workbook with a Dashboard Configuration with this table inside, along with multiple Pivot Tables (7 each) populating the Dashboard Data using Slicers. The issue was the file size was at 42MB and that is with it formatted as binary xlsb. So, I re-grouped and came up with an idea to remove this table and store the it in another workbook. Then I would use Power Query to link the Pivot Tables. At first I was excited because the workbook size fell from 42MB to just 2MB and the file was fast. For the Pivot Tables I unchecked "Save Source data with File" in Pivot Table Options. I also unchecked Refresh Data when opening the file options. In PQ I also disabled Enable Background Refresh and Refresh data when opening the file. The data in the Table will only change once a month and my plan was to refresh the data in the workbook when this occurred. My Issue is when I had another person open the file and try it out when they selected one of the slicers it stated the data had to be refreshed first (Not everyone has access to the table stored in another workbook and I don't want them to). So, I had them refresh anyway and it took a while (they had access but it took 3-4 mins to refresh). Once the refresh completed they were able to perform slicer selections successfully. Is there anyway to keep the file size low and make use of the data without refreshing it? If I don't use PQ and store the data within the file may get too large and too slow and no one will use it. If I use PQ and store the data remotely then while the file size may go down tremendously, data refresh has to occur and it takes way too long. I'm danged if I do and danged if I don't. On a side note: I got to learn Python so I can get myself out of these Excel nightmares.Solved1.2KViews0likes1Comment
Recent Blog Articles
Re: New in Excel for the web: Power Query Refresh is now generally available for selected data sourc
sowjvellore On your Desktop in your Type Here to Search bar type in Task Scheduler and you should see a program appear named Task Scheduler. If you Google Use Task Scheduler to Open Existing Excel Wo...0likes0CommentsRe: New in Excel for the web: Power Query Refresh is now generally available for selected data sourc
Hey sowjvellore Very much agree with your position. Perhaps here is an alternative solution to your problem that you might find is the better option. I am using Task Scheduler to automate opening o...0likes0CommentsRe: New in Excel for the web: Power Query Refresh is now generally available for selected data sourc
Is it possible to use Power Automate to refresh all data connections in an Excel Workbook? I am getting so many conflicting answers to this when I google it. I have some Power Queries in a workbook s...0likes0Comments