for an ad-free Fetcheveryone experience!

SpreadEveryone: The Fetchland Excel wire

102 watchers

Bowman 🇸🇪 Jul 2024 4:21pm, 11 Jul 2024 3,837 posts	Quote Pin Bowman 🇸🇪 You have to set an automatic update on PBI once a day at a time when you know you have a new data set. Or update and publish the pbix file.
JamieKai chameleon Jul 2024 5:38pm, 11 Jul 2024 18,942 posts	Quote Pin JamieKai chameleon Ah grand, thanks. So much I don't know, but need to know!
HappyG(rrr) Jul 2024 5:48pm, 11 Jul 2024 50,440 posts	Quote Pin HappyG(rrr) I was told my Excel fu was strong just cos I did some pivot tables with filters and a couple custom columns. It was 259K rows but that was just the data I was given! G
Bowman 🇸🇪 Jul 2024 6:46pm, 11 Jul 2024 3,841 posts	Quote Pin Bowman 🇸🇪 It’s all relative to someone else HappyG
JamieKai chameleon Jul 2024 7:04am, 12 Jul 2024 18,944 posts	Quote Pin JamieKai chameleon Yes, people rave about my Excel skills, but I'm really not that good. As someone who has never taken praise well it becomes quite awkward.
Pothunter Jul 2024 5:31pm, 12 Jul 2024 9,129 posts	Quote Pin Pothunter I had an interesting issue today. I export data from SAP into Excel on a regular basis. SAP outputs dates in the German format dd.mm.yyyy. I usually just do a search and replace to swap the dots for slashes and it turns them into “proper” dates. I’m automating a reconciliation report via macros at the moment so simply replicated the logic in code. Only, Excel decides to switch the dates that it can into US format. It would be ok if it did all of them, but it only screws up the ones that have a day less than 13. Found a solution online to do it with text to columns instead. Worksheets(“SAP”).Range(“E:E”).TextToColumns Destination:=Range(“E:E”), DataType:=xlDelimited, FieldInfo:=Array(1, 4) I suspect it is far more efficient than selection.replace too.
um Jul 2024 5:58pm, 12 Jul 2024 8,656 posts	Quote Pin um Silly question, but are all your preferences UK? Including the format?
Pothunter Jul 2024 7:38am, 13 Jul 2024 9,132 posts	Quote Pin Pothunter Yep! It seems to be a quirk of doing it in code. Works fine manually, not fine in code.
JCB Jul 2024 2:17pm, 13 Jul 2024 5,734 posts	Quote Pin JCB I have had the same problem with opening a CSV file. The 'greedy' automatic conversation of dates into US format, despite OS settings, etc. Had to manually do import from an open spreadsheet and then choose the option to not process values automatically (if I remember correctly). There are some settings in Excel for not auto-converting values. I seem to have to set these options back to not auto-converting from time to time. Not sure why it forgets them. Maybe a background update (work laptop).
Pothunter Jul 2024 4:53pm, 13 Jul 2024 9,138 posts	Quote Pin Pothunter With CSVs I’ve taken to doing an import data from CSV and then you can specify the format for each column. A real pain the way it tries to be clever!