In my experience it doesn’t work well when you have more than a couple people editing the file. My company had a group of ten modifying the same file in live time; it led to huge desync problems.
I live in Excel hell and even that made me shudder. Just work on separate files and have a master spreadsheet append everything with power query.
I made a similar reply higher up and I fucking hate that that’s a solution but it legitimately would work in this use case. I frequently deal with 1M+ row data sets and our API can only export like 20k rows at a time so I have a script make the pulls into a folder and I just PQ to append the whole fucking folder into one data set. You don’t even have to load the table at that point, you can pull as-is from the data model to BI or make a pivot or whatever else you’re trying to do with that much data.
Parent company doesn’t want ANYONE to have direct read access to the database - only the scant few heavily formatted reports the user-facing software will allow. Data analysis still needs to get done though, so…
Yeah. PQ -> Data Model saves my ass and my co-workers think I’m a wizard.
That, and learning how to quietly exploit minor vulnerabilities in the software to get raw tables I “shouldn’t” have and telling not one soul has been a winning combo!
It can handle more than a million rows so why not?
A million rows sounds like a lot until you need more than that
Database2.xlsx
Database2_not_locked(1).xlsx
In my experience it doesn’t work well when you have more than a couple people editing the file. My company had a group of ten modifying the same file in live time; it led to huge desync problems.
I live in Excel hell and even that made me shudder. Just work on separate files and have a master spreadsheet append everything with power query.
I made a similar reply higher up and I fucking hate that that’s a solution but it legitimately would work in this use case. I frequently deal with 1M+ row data sets and our API can only export like 20k rows at a time so I have a script make the pulls into a folder and I just PQ to append the whole fucking folder into one data set. You don’t even have to load the table at that point, you can pull as-is from the data model to BI or make a pivot or whatever else you’re trying to do with that much data.
Parent company doesn’t want ANYONE to have direct read access to the database - only the scant few heavily formatted reports the user-facing software will allow. Data analysis still needs to get done though, so…
Yeah. PQ -> Data Model saves my ass and my co-workers think I’m a wizard.
That, and learning how to quietly exploit minor vulnerabilities in the software to get raw tables I “shouldn’t” have and telling not one soul has been a winning combo!