Ed Hansberry
My feedback
-
1,690 votes237 comments · Excel for Windows (Desktop Application) » Data Import · Flag idea as inappropriate… · Admin →
We are actively working on this. Thanks for your votes!
Ed Hansberry
commented
@lauren - you absolutely need the data model to build pivot tables using Power Pivot, which gives you the ability to use multiple tables, relationships, measures, etc.
if you aren't using the data model, then you are using pivot tables the old way which requires a single flattened table in a spreadsheet. Horribly inefficient the larger the table is.
Ed Hansberry
commented
Thanks Rory. I actually use a dropdown in a cell in Excel to feed the source server info to the original SOURCE statement.
I don't want it automatic as sometimes I may want Power Query to ping my SQLEXPRESS copy even if at work if I am working with tables that rapidly change during the day, making troubleshooting and figuring stuff out a pain.
But interesting use of the try statement. :-)
Ed Hansberry
commented
@martinG - that is a good practice. I didn't know it sped things up. I do it because I keep replicas of some tables on my PC in SQL Express from our SQL Server so I can develop reports when not in the office. I simply change one data source in the query and dozens of tables automatically update to my local install, then I change it back when I get in the office or push to production.
Ed Hansberry
commented
"Hey please speed up the engine of Excel query b4 moving on to other less relevant features. "
AMEN! I am sure it isn't the same team, but when I am tapping my fingers on my desk waiting for PowerQuery to refresh itself after I simply rename a step and I see a blog post that now we have Lava Ink in Office when drawing, I want to throw my computer off of my desk.
Ed Hansberry
supported this idea
·
-
3 votes1 comment · Excel for Windows (Desktop Application) » Data Import · Flag idea as inappropriate… · Admin →Ed Hansberry
commented
Same idea a the Power BI site for PQ - https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/20038996-have-a-visual-indicator-of-steps-that-are-folding
Ed Hansberry
shared this idea
·
-
170 votes22 comments · Excel for Windows (Desktop Application) » Data Import · Flag idea as inappropriate… · Admin →
Hi Joana & others,
Thanks for logging this suggestion. Yes, being able to protect your queries would be quite useful indeed for delivering solutions. We’ll prioritize this based on the interest, so if you’re reading this and haven’t voted as yet, please do so.
thanks
Ashvini Sharma
Lead Program Manager
ExcelEd Hansberry
commented
I half wonder if there is no impetus at MS to do this because if you do this in Power BI, the query is automatically hidden from the end user, and they get a $10 Power BI license.
Ed Hansberry
commented
@regan - that would work but has serious limitations as it becomes difficult to quicky modify/tweak the query, and prevents it from being used in a MERGE or APPEND without breaking query folding. No subsequent tasks in the query would fold either.
Ed Hansberry
commented
Ryan, that does work, but it also causes DAX to fail. So if your query is loaded to a table, that is fine,but if it feeds PowerPivot, it causes PowerPivot to stop working, or anything that relies on the Data Model, like the CUBE functions.
Ed Hansberry
supported this idea
·
-
473 votes244 comments · Excel for Windows (Desktop Application) » PivotTables and Power Pivot · Flag idea as inappropriate… · Admin →
Hi all,
I’m happy to announce that your input has had another direct impact on the product. In this case, a decision has been made to have Power Pivot be available in all Windows editions (“SKUs”) of Excel! This means the Business, Home, Standard, etc.. all of these will have Power Pivot. As your comments have indicated, Power Pivot has moved from targeting a specialized audience to generally useful capability for working with data in spreadsheets. As such, it makes sense to make this as broadly available as possible.
We have started rolling this change out with the April Current Channel updates (Builds 9330 and beyond). I’m also happy to announce that these SKU changes will be reflected on the upcoming 2019 version as well.
Thank you for pushing for this!
Ashvini Sharma
PM Manager
ExcelEd Hansberry
commented
Because @Mimi - this was a licensing decision that involved little to no coding and was just a decision to include it with all Office SKUs for Windows. The one you linked to is a massive code change to go back to the old way. I doubt they will revert that given they are now targeting the new Sets feature in Windows Redstone 5 due this year, and it won't work effectively with all documents in a single container. But they are looking at specific use cases as the post says at the top.
Ed Hansberry
commented
No exact date for Office 2019, but scheduled for the 2nd half of this year, which could be July 1 or December 31, or somewhere in between. MS hasn't finalized a date yet.
Ed Hansberry
commented
For those questioning the Insider track, you don't have to do that. LIke all features, MS is apparently rolling this out to Fast Insider first, then will move to Slow Insider when a bit more stable, then to normals when fully tested.
If you want it right now, then yes, you need to get Fast Insider, but understand you are getting a beta version of ALL of the office apps and there are bugs that may impact you. MS doesn't recommend using Fast Insider on production machines.
Ed Hansberry
commented
@mark - Office 365 for home is not licensed for commercial use. If your clients are individuals that just pay you to help with their PC needs, that is fine (though seems odd that kind of person would care about Power Pivot), but if your ciients are small businesses, they need to have an Office 365 business license, not home.
Ed Hansberry
supported this idea
·
-
1 vote0 comments · Excel for Windows (Desktop Application) » Tables, Sorting and Filtering · Flag idea as inappropriate… · Admin →Ed Hansberry
shared this idea
·
-
820 votes
Thanks for all the votes and comments on this suggestion. We know this is important and it’s a big undertaking. Please continue to show your support for this.
Steve K [MS Excel]
Ed Hansberry
commented
If you read this thread on Answers, it looks like MS is making some internal progress on this feature... at least the Mac recognizes there is a data model. https://answers.microsoft.com/en-us/msoffice/forum/msoffice_officeinsider-mso_mac-msoinsider_excel/workbook-using-a-new-feature-in-excel-for-windows/8f18af1c-d9df-40e1-8646-49a0d22ffb85
Ed Hansberry
commented
Google has Power Pivot?
Ed Hansberry
supported this idea
·
-
2,480 votes
Hi everyone – There are many comments here asking for PowerPivot, which is being tracked separately. This suggestion is only counting votes for PowerQuery (Get & Transform).
To vote for the PowerPivot suggestion, please go to it – https://excel.uservoice.com/forums/304933-excel-for-mac/suggestions/19568977-add-powerpivot-to-mac-excel.
Thanks – Steve K [MS Excel]
Ed Hansberry
commented
This is actually over here https://excel.uservoice.com/forums/304933-excel-for-mac/suggestions/8995483-add-support-for-get-transform-formerly-power-qu with over 2000 votes! Add yours to it.
Ed Hansberry
commented
I think it is Power Query and Power Pivot are built on .NET framework and AFAIK, the full .NET framework isn't available on macOS, so either they have to rewrite both in C# or C++, or the .NET framework that is available on macOS has to be beefed up.
Even though Power Query is now native to Excel 2016 vai Get & Transform, it still launches .NET to get to the query builder. That is why both it and Power Pivot look so different from Excel. They are outside programs not part of the core Excel.exe file.
Ed Hansberry
commented
@marcos - I would think the main reason is the Dot Net framework. Power Query and Power Pivot are both written in Dot Net on Windows. There is a Dot net core framework available for the Mac, but my understanding is the core is missing a ton of features of the full framework. So to get PQ and PP on the Mac, either the dot net framework has to be substantially improved on the mac, or PQ/PP have to be rewritten in in a language for the Mac, which means two different apps, and MS is moving away from that. They just got the Office codebase sync'd up this year!
Ideally I'd like to see PQ and PP rewritten on Windows in C/C# to speed up the UI and performance, then used on the Mac as part of the common code base.
This is speculation on my part. I am not a developer or MS employee. But I do know dot net on the mac is severely lacking compared to the same on Windows.
Ed Hansberry
commented
Thanks. The parts are *very* different and didn't come to Excel for Windows at the same time, so wouldn't expect these to either. But... Power Query first!
Ed Hansberry
commented
@anna, that is what you should do. There is no "minimum threshold" to get a feature added. I've seen requests with 50 votes get added and there are some with over 3,000 votes that don't have a "working on it" or even a "under consideration" yet.
There is a cost factor, and unfortunately I think this one is pretty high. But it is safe to say, the more votes it gets,the more likely it is to happen.
Ed Hansberry
commented
Critical for Mac Office to be taken seriously for finance/accountants that automate a lot of data calculations with these tools.
Ed Hansberry
supported this idea
·
-
338 votesEd Hansberry
supported this idea
·
-
260 votes84 comments · Excel for Windows (Desktop Application) » Collaboration and Sharing · Flag idea as inappropriate… · Admin →
Hello All – If you haven’t already heard Coauthoring in Excel is live in production. In July we released co-authoring in Excel for Windows Desktop to all customers in our monthly (cc) current channel. Check out the details and the requirements for collaborating with others in the below support article and let us know how it goes. We’d love your feedback.
Thanks, Scott (Microsoft Excel PM)
Ed Hansberry
commented
@eric Johnson, the reason it requires the cloud is a TON of work is being done by the server, not the Excel clients.
Ed Hansberry
commented
@anonymous - it depends. If you are in the semi-annual (targeted) channel, you'll get an update in March that should have it.
If you are in the semi-annual channel (i.e. non-targeted) you won't see it until July.
See https://docs.microsoft.com/en-us/deployoffice/overview-of-update-channels-for-office-365-proplus for more info on when each channel gets the new goodies.
Ed Hansberry
commented
@caleb - this has been available to the Monthly Targeted channel (formerly insider slow) for at least 3 months. I am not sure if it is on the deferred channel yet.
Insider Fast has had it since February.
Ed Hansberry
commented
We'll Excel Help, since the MS Sharepoint/OneDrive servers are doing A LOT of the work to make collaboration in Excel possible, leaving it on your network won't help. We moved all of our files to OneDrive any teams which uses sharepoint for files. We have collaboration, file versioning, and no worries on backups.
And we like to think of ourselves as normal people.
Ed Hansberry
commented
@Anon - stuff stays in fast ring as long as it takes. This was a pretty big/invasive feature to the product so took a lot of testing. I suspect it will stay in slow ring for a while too as they add more users to it and hammer out more stuff. Excel still isn't aggressive enough in pushing the final changes back to the server before closing. I can have a large file, click save then close and my changes are stuck on my machine. I notice when I open the file again and Excel tells me there are unsaved changes to save.
That has to be fixed before it rolls out to the deferred/stable channels.
Ed Hansberry
commented
Mike, understand that, but I would say short of Windows, Excel is the most complex product MS makes. Data tables, Power Pivot, Power Query, Consolidation tools, the other Data tools (remove dupes, data validation, etc.) filtering, forecasting tools, outlining tools, watch window, auditing tools, charting tools, conditional formatting, etc.
Just consider the complex calculation engine Excel has. See http://www.decisionmodels.com/calcsecrets.htm for more on this (including the sublinks), but now imagine making this work with multiple people simultaneously to both minimize the impact of someone keying data or editing formulas without grinding everyone in the file to a halt, or locking up the file. That is just one aspect of it. PPT and Word have nothing like this, and yet this is the very core of Excel.
Excel is a magnitude more capable than any other Office app, and at least an order of magnitude more complex.
Additionally, it appears to support local 3rd party addins when collaborating without affecting others in the file at the same time.
I also wish this had arrived 18 months ago, but I fully understand that if it ONLY got the same due diligence that Word and PowerPoint got, we'd be 2020 or later getting this feature. I suspect it got far far more diligence just to get it where it is today.
Give the team credit where credit is due. For those that see Excel as Microsoft's version of Google Spreadsheet and just grasp the spreadsheet with some formulas are truly missing so much of what is really underneath the hood.
Ed Hansberry
commented
Mike, it is a bit easier to do on Google Spreadsheets since that product has perhaps 20% of the functionality of Excel. I've used both Google Docs and Office for many years, so understand the differences.
Ed Hansberry
commented
It is here! Must be on the Fast Ring for now though. https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_win10/co-authoring-comes-to-excel-on-windows-desktops/42ea1b8e-6601-4e53-aeb7-381e50387125?tm=1490719962759
Ed Hansberry
commented
That said Brian, it used to be on the roadmap, and no longer is. https://products.office.com/en-us/business/office-365-roadmap?filters=excel
I'm not sure how much stock I'd put in a promise from May 2016 10 months later.
Ed Hansberry
commented
I'd be happy if the desktop editing was limited. Meaning some more advanced features like PowerQuery, PowerPivot, array formulas, and some other items were blocked during a co-authoring session. It just needs to be more powerful than the online version. For example, when building a model with someone else, the inability to set up conditional formatting is a show stopper for us as that gives visual indicators that something is wrong or missing in the model when it ultimately goes to end users to use.
Ed Hansberry
commented
Thanks for the info Mike. I am going to report this through the happy/sad face utility in Excel as a bug. I suggest you do the same since our OneDrive scenarios are different. It must be a glitch in Excel.
Ed Hansberry
commented
Mike, are you by chance using the NextGen client for Sharepoint sites that was released in preview in Sept? I am seeing the same behavior. I think it is the OneDrive client causing this issue, not Excel itself.
My current workaround is to open the file, and if I'll be editing, I immediately save it. If it saves, I am ok. If not, I close and reopen until it will save and I don't lose work.
Ed Hansberry
commented
@dan - a year later, any updates? Is this going to happen? I know that the old "shared file" method for your local network was iffy at best and excluded files with Tables among other things. so wondering if this is even possible. Hoping it is, because we really need it. The Excel Cloud version is too anemic to even open many of our files - due to size or embedded images.
Ed Hansberry
commented
It bothers me that this isn't listed on the Office 365 Roadmap page. http://fasttrack.microsoft.com/roadmap?filters=excel
In fact, if you filter by Excel, there are no new features for O365 users in an O365 environment. I know the Excel team is adding features like recent Power Query enhancements, but that isn't specific to O365 business clients like this requested feature is given you need Sharepoint in the middle coordinating the collaboration.
Ed Hansberry
commented
This is so badly needed. Word and PowerPoint already have this. And please - don't exclude files with tables in them. The "SHARE" feature in Excel used to be pretty good until 2007 when tables were introduced, and using that awesome feature eliminated the ability to share files.
Ed Hansberry
supported this idea
·
-
1,319 votes382 comments · Excel for Windows (Desktop Application) » Editing · Flag idea as inappropriate… · Admin →
Hi There
Thanks for the interest and discussion on this topic. To help us with planning, could you please answer this brief survey?
Thank you!
Best, Urmi [MS XL]
Ed Hansberry
commented
@exacerbated. Yup, definitely happens. It is now second nature for me to press the windows menu key (usually to the right of the space bar), and the SV.
That is Paste Special, Values.
What is happening is the cell is formatted text, you are copying from somewhere else, even another app, and it brings a number format with it, then excel trashes it.
If you paste as values, only the raw ASCII gets pasted in there, no formats, so the Text format in Excel persists.
Ed Hansberry
commented
@Exacerbated - make sure the table column with the long digits is formatted in Excel as TEXT format. Editing/entering data will retain the text format and not convert to a number or lose precision.
Ed Hansberry
commented
Power Query isn't a third party addon. And for importing text files, the learning curve is almost nil. It is far easier to use than the depreciated Text Import wizard, and far more accurate than just clicking on a CSV file and hoping it reads your mind.
Ed Hansberry
commented
@corin - what you refer to is not the same thing. A part number with an "E" in it is not the same issue, but you are correct, Excel will try to convert to a number thinking it is scientific notation, unless you use Power Query (which you should) where you can tell it to leave that field as text. No one should be importing CSV files via plain old Excel if they want any level of control. Import via Power Query and get 100% control over every field.
Ed Hansberry
commented
If you type in 123456789 into a narrow cell, it will change it to 1.2E+08 visually, but if you change the cell format to comma, currency, or whatever, it has retained 123456789 as the full precision.
Ed Hansberry
commented
Ok. that is massive. M.******I.V.E. They could be working on that, but that would literally involve changing every single function that deals numbers (so all but the text functions), tables, pivot tables, Power Query, Power Pivot, Power Map, the Power BI enterprise app, Data Gateway, which all rely on the same 15 digit precision.
Ed Hansberry
commented
It does that Zak. The issue only becomes an actual data problem when there are more than 15 digits. Excel cannot handle that so if you put in an 18 digit number, regardless of how it displays, it will change the last 3 digits to zero - unless you enter it as text.
Ed Hansberry
commented
I see you've never talked to corporate lawyers before.
And much like you have shot down my ideas on using Power Query which resolves about 95%-99% of the issues in this thread.
Have a nice evening.
Ed Hansberry
commented
You cannot put anyting on sheets taht isn't saved. It is automatically saved. You would have to close it, delete it, then empty the trash, and even then, not sure if there isn't a 2nd level trash can it is stored in for a while. Office 365 has that.
And again, in a big corporation, permission for that involves lawyers, not your boss.
Ed Hansberry
commented
Some of you don't understand how large corporations work. An employee can get fired for putting company data on ANY unapproved cloud solution.
Ed Hansberry
commented
@Scott, Power Query does exist. The G&T name is for the Excel UI, Once in the query UI it is still referred to as Power Query, and is also used in Power BI.
As to complexity, for handling CSV files, you don't need to know any M code. It is all push button in the UI.
As for DAX, if you are using DAX in side of Excel via Power Pivot, you must learn Power Query. PQ is where you bring in your data, clean it, and create a structure for your data so it fits a clean Star schema, which is where DAX shines.
I found out the hard way bringing in data directly via DAX was going to be a train wreck for all but the smallest data sets.
Ed Hansberry
commented
@Corin, I think your mention of PowerShell is the first in this thread. I know PS is very powerful, but I have no idea how you'd use it with CSV files.
Ed Hansberry
commented
Jody, solid workarounds (namely, Power Query) have been shown how to 100% avoid this issue EVERY SINGLE TIME, but some on this board would rather just rant.
You are spot on RE: Google Sheets. Compared to Excel, that platform is a joke. It is like comparing a word processor to Notepad.exe.
Ed Hansberry
commented
@corin - yes. You'll need to manually edit the code, but absolutely. http://www.thebiccountant.com/2017/01/09/dynamic-bulk-type-transformation-in-power-query-power-bi-and-m/
Ed Hansberry
commented
Corin and Todd, I only did the simplest of steps for a single file. I have power queries that look at a folder and always import the most recent two files which can be done based on file date OR contents of the file, such as an ASCII print header or text field, and then compares them. The file names are irrelevant in those cases.
Export. Save. Refresh.
That’s it.
Ed Hansberry
commented
@corin - I never said people were relying on scientific notation. I said they were relying on the way it works today and have coded, made workflows, or formulas that handle it as is, then modify it as their needs dictate. If you change how the data comes in, that all breaks.
My solution is not hacky - far from it. You should learn how to use Power Query. Try my steps and get a feel for it. This is what it is FANTASTIC at doing. You do the many steps ONE TIME. Your steps are recorded. Then you just refresh all and all those steps are applied again with a new CSV file. It is the total opposite of a hacky workaround.
I never said MS ignoring it for decades was ok. I said the system has existed as it is for decades, and as such, people have modified their models accordingly. Furthermore, MS is NOT ignoring it. They addressed it above, and they addressed it in the Reddit AMA earlier this week. It is on the table for discussion, but I know a big sticking point is how not to break models that have been in existence for years, or decades, by changing behavior of what Excel does while still making it easier to work for people that don't know about Power Query, or that consider such an approach "hacky." LOL!
Personally, with the advent of Power Query in Excel 2013, I would NEVER import a CSV file like you are doing. PQ gives me the ability to fix each field as I need, adjust the contents as I need, then dump to a table *EXACTLY* the way I want it, and then it does it day in and day out, months on end and I am never surprised at the results because I. DO. NOT. LET. EXCEL. MAKE. ANY. ASSUMPTIONS.
PQ lets me control 100% of it. It works with leading zeros, text like 6E034, 50 digit numbers, etc.
I personally don't care what MS does in this regard because I use the Power Query/Get & Transform model to control the data.
What is hacky is double-clicking on a CSV file and then modifying it to suit your needs every time.
Continue on though. I was just trying to give you a real world solution that works today hoping to save frustration until MS does whatever they will do whenever they may do it, which might be nothing and never. Or it could be a magical solution that pleases everyone and be released to the fast ring tomorrow. My solution is there today though, and I'd bet money if many people implemented it, they wouldn't even notice if MS ever did anything about the CSV import via the file menu or the old now depreciated Text Import Wizard.
Ed Hansberry
commented
Daniel, not sure why the query method fails for you. This works 100% of the time for me:
1) Data, Get Data (This is Excel 2016 - Excel 2013 is the Power Query addin)
2) CSV file
3) Edit button
4) Change Data Type to Text - tell it to "Replace" if it converted to a number first, which it did with my credit card number test.
5) Close and Load to a sheet (or data model if you need this in a Pivot table)Even preserved leading zeros.
Save the Excel file.
Get new CSV and drop it where the old one was with the same name.
Open Excel File, Data Tab, then REFRESH ALL.
Works every time.
Ed Hansberry
commented
What was fixed in a later release?
Also, I assume nothing. MS assumes they know how their product is being used by both regular users and fringe users, and they won't change something that breaks it for either.
Ed Hansberry
commented
It isn't a simple thing to fix. Right or wrong, this is how it has worked for literally decades. Millions of spreadsheets are set up to work this way, and then adjust accordingly if the user wants the end result to be different.
If MS "just changed it" millions of models would break overnight. So they have to move carefully to provide a better answer. Google doesn't have this problem. Their sheet is new and they did whatever they wanted to do, and honestly, if it changed something, some would complain, most wouldn't notice, and fewer would care. Excel is very different. Databases are built on it. Visual Basic code is built on it. .COM addins are.
I know MS is working on this very issue. It was in their recent Reddit AMA on Wednesday of this week. But it is far from a "fairly simple issue to resolve."
And the funny thing is, 99.9% of the issues in this thread are avoidable by using the Get & Transform feature in Excel 2013 and 2016 (also known as Power Query) or preformatting cells as text. So it isn't like you cannot use Excel to get the job done. You just have to do a few extra steps that actually GUARANTEE you the results you are looking for vs having the app make the decision for you.
Ed Hansberry
commented
Zak, it isn't about using an inaccurate number. It is about using a number where you can trust the number of significant digits. Most numbers in science are rounded to some number of significant digits, unless you know exactly. For example the number of people on this thread fighting windmills is 1. Exactly 1. That is an exact and scientific number.
Ed Hansberry
commented
I suspect this is the fix, which will be part of Office 2019 released in 2018. Just speculation though. Not a lot given in this announcement.
https://techcrunch.com/2017/09/26/microsoft-excel-is-about-to-get-a-lot-smarter/
Ed Hansberry
commented
Seems to me a lot of this could be fixed if there were some addtional "autocorrect" options for Excel that we could disable.
*Stop changing text to dates. If I type "Oct 2015" then I want "Oct 2015" in the cell, not 10/1/2015 formatted as MMM-YY
*Stop changing math to dates. =9/12 is not my way of telling Excel to convert it to Sept 12 in DD-MMM format.
*stop changing math to fractions. I cannot repro an example right now but too many times I've entered something like =8/6 because that is what I need in the cell but Excel will change it into a fraction, causing me to undo/redo until it stops.
*The large numbers issue here (though that won't address people importing digits larger that 15 places as that is the max excel handles, all the rest becoming zero)Ed Hansberry
commented
@zak - load two files (your favorite - CSV files) of something modest. Say 200,000 rows, 10 columns for one file, and 5,000 rows, 30 columns for another. Join them using two columns (like a customer number and state field), then report the summary by state and month. And for grins, the customer numbers all have 30 leading zeros.
This is about 2-3 minutes work in Excel, including the preservation of the leading zeros. Let me know how Google Sheets works out for you.
Ed Hansberry
commented
"The issue with Google Sheets is that..."
And the fact that compared to Excel, Google Sheets is not even as powerful as Microsoft Works was. And I used Google Sheets at my prior company for 3 years.
Ed Hansberry
commented
FWIW, I am not posting workarounds so MS doesn't have to fix it. I am posting workarounds so people can get their jobs done TODAY because whatever solution MS does evenutally come up with, it will change 20+ years of how Excel deals with some things, and in any event, I suspect MS will not even take changing how many digits Excel can handle (15) into consideration. As for the CSV stuff, yeah, it isn't ideal how MS handles it today.
So, use the workarounds provided for now until whatever John [MS XL] posted about that is under review has something done about it, or wait.
If I waited until everything was perfect to do my job I'd be fired on the spot. My whole job is workarounds. And I am thankful for them. Job security. :-)
Ed Hansberry
commented
@brent - PowerQuery is a great solution to this. But the expectation here is Excel to be a CSV file editor, which was never the intent.
I use PowerQuery all of the time with CSV files to read data into Excel, but I use Access if I want to control exactly how CSV file output is done.
Ed Hansberry
commented
" The big problem I have now is with account numbers that have a letter at the end, power query throws an error when trying to convert them to number format."
It is because that isn't a number. Use the Number.FromText function inside a Try/Otherwise command.
Ed Hansberry
commented
@zak - you should read up on the Excel calculation engine at http://www.decisionmodels.com/calcsecrets.htm. Being 16 bit, 32 bit, or 64 bit, has nothing to do with it. It has to do with how it was written and changing that would have massive MASSIVE issues to overcome. Not impossible, but not trivial.
If Excel suddenly changed and said "ok, every thing I import that I cannot handle, I need to convert to text" that would break MILLIONS of models that have been written in companies since the 80's. I have had this issue in the past and I wrote models around the issue using import templates early on, VBA import routines, and now PowerQuery. If Excel changed now, it would break what I had others have currently written. Or if you expect it to be converted and understand the 15 significant digit limitation and know it isn't material to your model, then those models would be trashed if those values suddenly came in as text.
That is the problem MS faces. They cannot just make a change to make it work for this without understanding how it will affect things that have been in existence for decades. It is like the 1900 date system in Excel where they screwed up and messed up the leap year when they programmed it. (or maybe it was Lotus that did that and Excel emulated it) If they just "fixed it" then people that had written functions to overcome the issue would suddenly find all of their calculations broken.
Excel isn't like apps on your iPhone that can just be changed on a whim. This is not a simple issue for MS to fix. I do hope they come up with a resolution, because there is definitely a small army of people that seem to want to put credit card numbers in Excel and have it magically figure out what to do with it.
Or, you could use PowerQuery in Excel and get around this. Or you could switch to Google Docs which doesn't have to worry about changing things like this, because no one with sophisticated models uses GDocs so no one would care if something changed.
Ed Hansberry
commented
@zak, you are comparing apples and catalytic converters. Notepad isn't doing anything. It is simply showing you what is in the file. Excel has to process numbers, and the calculation engine is only capable of handling 15 digits. To change that would be an insanely massive undertaking. Every computer program in the world the processes numbers has a limit on the significant digits it can handle. Solar calculators is usually 8. Excel is 15. Engineering software may be 30 or more. Software used in physics and math may be 100. Whatever it is, when you exceed that limit, the software will start turning digits to 0 on the right to preserve the numbers on the left.
If you want Excel to process it as text, then tell Excel it is text. Put it in quotes, or use PowerQuery to force it to text before loading it to a sheet or the data model.
Ed Hansberry
commented
@zak - Power Query eliminates the hassle as you only write the query one time. You don't go through that horrible import dialog box.
The problem is there is no standard for CSV files, but the rule of thumb is, numbers that should be treated as text should be enclosed with quotes. If you type 123456789123456789 into Excel, it will remove the last 5 digits because it simply cannot handle numbers with more than 15 significant digits.
I am not defending how Excel does it. I personally hate the autoformat garbage it does all throughout the app. Type "1 1/2" and excel assumes I want a fraction. It changes it to 1.5, then formats as a fraction. I have to undo that and force the cell as text format, or preceed with a ' character (single quote)
I am merely showing a way to avoid the issue. If you want more than 15 digits, you need to switch products. MS Access supports 28 significant digits for example if you make the field decimal.
And I just formatted a cell as text, put in a 21 char "number" into that cell, and saved as a CSV. Opened in Notepad and there was no truncation. Excel correctly saved all 21 digits to the file. It didn't put quotes around it though, so when I opened it up, it did truncate. When I imported via Power Query though, it kept all digits.
Ed Hansberry
commented
@Steve Burkhart, Excel's conversion to scientific notation isn't losing your data. Excel simply cannot handle more than 15 digits in a number. Period. If the file you are exporting is creating a CSV, you need to have it wrap the number in quotes. Excel will handle as text.
Or use Power Query built into Excel (2016) to do the import. There you can force the import as text and then when you load it to an Excel spreadsheet, it will come out as text and all your digits will be intact.
If you have 2013, you need to download the Power Query addin. I don't think it exists for 2010, and know it doesn't for 2007.
Ed Hansberry
commented
http://dailydoseofexcel.com/archives/2017/04/05/converting-numbers-to-text/ has info on how to put credit card numbers in Excel. The issue is Excel isn't broken, it just isn't being used properly. CC numbers aren't numbers, they should be strings, and should be entered as such. Excel cannot even handle a 16 digit number, which many CC #'s are.
Ed Hansberry
commented
Access can track it, but so can Excel. you just need to format the cell as TEXT before pasting the values in. That is what the TEXT format is for. Excel cannot handle a 22 digit number. Neither can Access for that matter. You'd need to format the table field as a TEXT/STRING field as well before adding the data in.
Ed Hansberry
commented
Roger, Excel doesn't destry numbers. It just converts the format, You can change the format back. That said, Excel only supports 15 digits. Entering 1234567890123456 will result in 1234567890123450 being in the cell.
For things like tracking numbers and IDs, those should be text anyway. In our company, anything that is any type of ID is always text, even zip codes, which are only 5 digits.
Ed Hansberry
supported this idea
·
-
4 votes0 comments · Excel for Windows (Desktop Application) » Data Import · Flag idea as inappropriate… · Admin →Ed Hansberry
supported this idea
·
-
17 votes5 comments · Excel for Windows (Desktop Application) » Collaboration and Sharing · Flag idea as inappropriate… · Admin →Ed Hansberry
commented
Not sure how this would work. Unless you are talking about queries that take hundreds of hours (in which case, Excel is the wrong tool anyway), like the distributed computing projects that are online, anything you did locally on your LAN would be slowed down by the LAN itself. You'd need a server to parse out the queries into chunks, but then why not let the server do the work for you?
-
336 votes8 comments · Excel for Windows (Desktop Application) » PivotTables and Power Pivot · Flag idea as inappropriate… · Admin →
Hi Ken,
Thanks for logging this one, and thanks to others for voting this up.. We’ve actually had this on our list to get to for some time and have been balancing doing this against other… The more the community helps us rank it higher, the better the signal on overall importance… so, for those that haven’t voted for this but want it, do vote!
thanks
ash [MS XL]Ed Hansberry
supported this idea
·
-
2 votes1 comment · Excel for Windows (Desktop Application) » PivotTables and Power Pivot · Flag idea as inappropriate… · Admin →Ed Hansberry
commented
This was added to Excel 2016 months ago if you have an Office 365 subscription. If not, it will likely be in Excel 2018 or 2019, whatever they call it. My guess is when Excel 2018 hits the streets, everyone with Office 365 will have already had those features since O365 updates every month.
-
689 votes258 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →
Progress! As of this morning, we’ve begun enabling the first iteration of Excel’s new data types for a percentage of Office Insiders, including the ability to pull and refresh live stock quotes (and other financial data) straight in to Excel! We’ll be increasing the percentage of Insiders with access to the feature over the coming days and weeks, so if you don’t see it right away please hang in there!*
We acknowledge that right now this is only half of the suggestion (pull current stock prices), but historical data is in our sights and we’re planning to add this and many other improvements to stock and financial data. Your feedback thus far has been immensely valuable in helping us plan and design this work, and we look forward to your continued suggestions for how we can improve to better meet your needs
Again, we genuinely appreciate your involvement as we…
Ed Hansberry
commented
Good grief people. Microsoft didn’t disable anything. Yahoo pulled the plug. I hope MS will get another partner on this, or do it themselves is MSN Money. To think this is illegal...LOL
Ed Hansberry
commented
This is the risk of using a service you don’t control. Too bad Yahoo dropped this feature. Would be nice if MS added this ability via MSN. Of course Windows users can use Power Query/Get & Transform in Excel 2010 and higher, but that doesn’t help Mac users.
Ed Hansberry
supported this idea
·
-
174 votes3 comments · Excel for Windows (Desktop Application) » Data Import · Flag idea as inappropriate… · Admin →
Interesting suggestion, Wyn. Thanks for logging it.
Others coming in new to this, please help us prioritize this item by providing your votes.
thanks
Ashvini Sharma
Lead Program Manager
ExcelEd Hansberry
supported this idea
·
-
269 votes34 comments · Excel for Windows (Desktop Application) » Formatting · Flag idea as inappropriate… · Admin →Ed Hansberry
supported this idea
·
-
12 votes2 comments · Excel for Windows (Desktop Application) » PivotTables and Power Pivot · Flag idea as inappropriate… · Admin →Ed Hansberry
commented
This would be very helpful, and aid in building models in Excel before putting in Power BI. The CROSSFILTER function allows it, but is unnecessary in Power BI and adds work.
Ed Hansberry
supported this idea
·
-
76 votesAccepting Votes · 9 comments · Excel Online » Collaboration and Sharing · Flag idea as inappropriate… · Admin →Ed Hansberry
commented
This already works on files with Sharepoint. Hoping a OneDrive solution is on the way.
Ed Hansberry
supported this idea
·
Ed Hansberry
commented
I should be able to subscribe to any file in OneDrive, OneDrive for Business, or any Sharepoint site in Office 365 to get an email the next morning when the file was updated.
-
87 votesEd Hansberry
commented
@tim - they are not locking the offline file. Excel for PC and Mac both support collaborative editing with the online version now, as do the iOS and Android versions.
This wasn't true 60 days ago, but MS rolled that out for Excel in Office 365 just recently.
Ed Hansberry
commented
@tom - I know of no way to force Excel online use, but you could be creative with a formula. The Function INFO() doesn't work in Excel online, so if you put that in a file, like =info("Directory") Excel desktop will return the user's directory, but Excel Online will report #Value!. Wrap that in an ISERROR() function, you could have a warning at the top of the file.
=IF(ISERROR(INFO("Directory")),"","Please close this file in Desktop excel and use Excel Online. Thanks.")
That will be blank in Excel Online, but show the warning in Excel desktop. Add some red conditional formatting for even more attention.
That said, Excel in the Fast Ring currently supports real time collaboration, so hopefully this will be temporary for you. But I don't know when Excel in the Current or Deferred channels will get this new feature that is in beta right now.
Ed Hansberry
commented
Hubert - just create a single cell watch window. Excel Online will mandate the file be opened in Excel. It won't even try.
Ed Hansberry
supported this idea
·
@anon- if you don't have Office 365, meaning you have a perpetual license for 2016, you'll have to buy Office 2019 or subscribe to Office 365.
If you have Office 2010 or 2013, then I don't know if/when they will add this functionality to that Power Query addin. I kinda doubt they will, but who knows. But I doubt they will issue an update to Office 2016 perpetual license users. They never issue new features for perpetual licenses, just security updates.