10 Million Rows of data Analyzed using Excel's Data Model

Подписаться 89 тыс.

Просмотров 108 тыс.

50% 1

The Excel Data Model (also referred to as Power Pivot) can handle millions of rows of data and can replace the need for millions of LOOKUP formulas
Link to download CSV Files and Demo Excel File
aasolutions.sharepoint.com/:f...
00:00 Intro
00:42 Using Power Query to Consolidate files from a Folder
03:40 Changing Data Types to reduce the file size and improve performance
05:40 Recommend 64 Bit Excel
05:50 The Data Model and creating relationships - avoiding100 Million Formulas!
07:00 Creating a Pivot Table / Pivot Chart
07:45 Adding a Slicer
08:06 Presenting the data in a 3D Map / Globe visual.
The team and I at Access Analytic develop Power BI and Excel solutions for clients in Australia and deliver training around the World. accessanalytic.com.au/
Did you know I've written a book "Power BI for the Excel Analyst"?
pbi.guide/book/
Connect with me
wyn.bio.link/

Хобби

Опубликовано:

3 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 312

@kleb5208 Год назад

Wow! You have changed my understanding of Excel capabilities!!! I used to use MS Access to do what you did with Power Query in seconds. I wonder if Access has much use still! Brilliant video: super clear, concise, well produced and useful! Thank you!

@AccessAnalytic Год назад

Thank you for the very kind comments. Access is indeed being slowly made obsolete by several other Microsoft technologies

@okezieodochi4497 Год назад

This is AMAZING!!!! Still in awe. Thanks for the brilliant content.

@AccessAnalytic Год назад

You’re welcome

@shrirajdeshpande8125 3 года назад

What a beautiful way of explaining how to handle big data. Loved this !!

@AccessAnalytic 3 года назад

Thank you Shiraj, glad you enjoyed it

@dimitriosg 2 года назад

That is SUPER AWESOME! 😍 Thank you so much, Wyn!

@AccessAnalytic 2 года назад

No worries, you're welcome Dimitris

@IvanCortinas_ES 3 года назад

This is a powerful example from the Big Data era. Amazing video! Thank you Wyn!

@AccessAnalytic 3 года назад

Glad you liked it Iván

@marksheppard9260 2 года назад

Thank you, as many say, short and precise. Perfect

@AccessAnalytic 2 года назад

Cheers Mark

@byregowdahanumanthagowda6434 3 года назад

Short and precise explanation. This is amazing and thank you for providing data set for practice.

@AccessAnalytic 3 года назад

Thank you Byregowda, kind of you to leave a comment

@JamestheZinHondo 2 года назад

This is helped me out TREMENDOUSLY! Thank you!

@AccessAnalytic 2 года назад

That’s great Joe. Thanks for letting me know

@JTFSIX 2 года назад

OMG, I am sitting here Wide-Eyed and Overwhelmed with Joy...Thank you so much for this.....I have been racking my brain on how to reduce and control our data to a professional and organized manner!...AMAZING!!!

@AccessAnalytic 2 года назад

Great to know

@JTFSIX 2 года назад

SUBSCRIBED AND FOLLOWING

@natah1284 2 года назад

You took the words out of me! 😁 following too.

@AccessAnalytic 2 года назад

Thanks @@natah1284

@houstonvanhoy7767 Год назад

Very nice, Wyn! Thank you.

@AccessAnalytic Год назад

Cheers Houston

@Luciano_mp 3 года назад

Power Query, Data model, very efficient. It became much easier to work with such heavy data. Thank you so much!👍

@AccessAnalytic 3 года назад

Thanks for leaving a comment Luciano

@roberth.9558 Год назад

Well done. Thank you for the instruction.

@AccessAnalytic Год назад

Thanks

@assignfield6256 2 года назад

Really awesome !!

@mujtabaahmed8851 Год назад

Excellent tutorial, thank you!

@AccessAnalytic Год назад

Cheers!

@timdowden7725 2 года назад

Thank you for producing this excellent video - very easy to follow and very informative

@AccessAnalytic 2 года назад

You’re welcome Tim

@natah1284 2 года назад

Pretty awesome you are right!! Thank you for your videos are so easy to follow!

@AccessAnalytic 2 года назад

Thank you so much

@donharrold1375 9 дней назад

Great job! Incredibly powerful!

@AccessAnalytic 9 дней назад

Cheers

@thuhangtran4578 Год назад

Very well explained and easy to understand. Thank you.

@AccessAnalytic Год назад

Thank you

@husseinal-omaisi3758 2 года назад

You saved me a lot of time. Wonderful explanation.

@AccessAnalytic 2 года назад

You’re welcome Hussein

@salvadororopeza733 2 года назад

Great tutorial! Thank you

@AccessAnalytic 2 года назад

You're welcome Salvador

@phoebefleming9179 2 года назад

This is fantastic. I feel like I have a new superpower. Great explanation video.

@AccessAnalytic 2 года назад

That’s great Phoebe! Thanks for letting me know.

@demris15 Год назад

I've seen this a few times before, but no one has ever drilled down into the performance ROI... thank you, this is going to help a lot!

@AccessAnalytic Год назад

You’re welcome

@rkytac 3 года назад

Tremendous. Thanks a lot for knowledge sharing.

@AccessAnalytic 3 года назад

Thank you so much Raghavendra

@edsmith5355 3 года назад

Fantastic, thank you for sharing your knowledge

@AccessAnalytic 3 года назад

You’re welcome Ed, thanks fir leaving a comment

@bhuvaneshbabu8238 Год назад

Just amazing.. Your decimal rounding of to reduce space ... worked brilliantly for my data model. Data model pull from SSAS. Thank you so much.

@AccessAnalytic Год назад

Glad to help 😀

@chautran4347 2 года назад

Awesome! Thanks for your sharing.

@AccessAnalytic 2 года назад

No worries

@matusjaniga1165 7 месяцев назад

this looks super cool, thank you very much :)

@AccessAnalytic 7 месяцев назад

You’re welcome

@anthonyfernandezgonzalez8262 Год назад

Thanks, lesson completed. Greetings from Costa Rica.

@AccessAnalytic Год назад

No worries 🇨🇷

@jazzista1967 3 года назад

forgot about the decimal trick. That was awesome . So just by trimming the decimals to rounded numbers , you can reduce your file size. I am going to try that at work

@AccessAnalytic 3 года назад

Excellent

@Dayzdeee 3 месяца назад

This is great! Thanks 😊

@AccessAnalytic 3 месяца назад

You’re welcome

@alvinestrada1655 Год назад

I’m currently working on +11k rows of data and currently experiencing challenges. Glad I came across your YT channel 😃

@AccessAnalytic Год назад

Good to know, thanks for the kind comments

@Pankaj-Verma- 6 месяцев назад

That was awesome.

@AccessAnalytic 6 месяцев назад

Thanks :)

@aidaschart9154 2 года назад

I can't say how much I am thankful for this video,i struggled for two days and everything I tried end with not responding, you save me thank youuuuuu🤩

@AccessAnalytic 2 года назад

Glad it helped

@jazzista1967 3 года назад

Wynn... this was amazing.. i completely forgot the 3D maps... great video!

@AccessAnalytic 3 года назад

Thanks for taking the time to leave a kind comment, I appreciate it,

@alvinestrada1655 Год назад

Amazing!

@colinadevivero 5 месяцев назад

One of your best videos 😊

@AccessAnalytic 5 месяцев назад

Thank you

@caioferreiradesousa6742 2 года назад

This was mega! Thanks for sharing your knowledge mate! Appreciate it.

@AccessAnalytic 2 года назад

No worries, thanks for taking the time to leave a kind comment

@AccessAnalytic 2 года назад

No worries, thanks for taking the time to leave a kind comment

@AccessAnalytic 2 года назад

No worries, thanks for taking the time to leave a kind comment

@AccessAnalytic 2 года назад

No worries, thanks for taking the time to leave a kind comment

@tsdsummfiles9486 2 года назад

Thank you very much Access Analytic for sharing your knowledge in handling big data using ower query ..God bless

@AccessAnalytic 2 года назад

Thank you

@SB-vy9je 2 года назад

Ohmygosh, that, my friend, was simply amazing!! 👏👏👏👏 I can't explain to you how many files I have that are so large that they respond slowly and then eventually crash! I've been trying to save these large files as .xlsb, and it helps, but it isn't fixing the ultimate problem. I cannot wait to try out this technique to see how it affects my monthly reports. Thank you so much for taking the time to go through this exercise. I am officially a subscriber!

@AccessAnalytic 2 года назад

That’s great, glad to help flag what Excel is capable of

@JTFSIX 2 года назад

You have no clue the amount of sleep I have lost trying to figure out how to control this amount of data

@edroco623 2 года назад

Good Video. Very helpful and now ready to try it.

@AccessAnalytic 2 года назад

Thanks for leaving a comment Ed. Good luck with it!

@amand9038 Год назад

Amazing stuff

@AccessAnalytic Год назад

Thank you

@elfridhasman4181 2 года назад

Awesome, awesome, Thank You Very Much :)

@AccessAnalytic 2 года назад

You're welcome Elfrid

@ForzaJonesy 2 года назад

fantastic video thanks!

@AccessAnalytic 2 года назад

You're welcome Simon

@hoppers6899 3 месяца назад

Amazing!! Working up a prototype model in MS Access but wanted to report it in excel with pivots and slicers. Was ready to give up and hand it over to the BI developers but tried your method. 6m rows and extremely responsive. Also used your whole number trick and file size only 22mb. Thanks heaps!

@AccessAnalytic 3 месяца назад

Awesome, I really appreciate you letting me know you found it useful

@rockfighter9974 Год назад

Thanks for the video

@AccessAnalytic Год назад

You’re welcome

@jrklz2008 Год назад

Great !!! thank you

@AccessAnalytic Год назад

You’re welcome

@flyingeagle3750 2 года назад

You saved my life 🙏

@AccessAnalytic 2 года назад

Glad to help :)

@maynguyen8129 7 месяцев назад

The 3 tips in Excel video took me here. Thank you so much for the short but very informative tutorial video. I was trying to compare almost 9,000 rows with a couple hundreds rows, and Excel kept saying “Not responding.” I used the transpose function which is very very helpful without knowing it’s Power Query.

@AccessAnalytic 7 месяцев назад

You’re welcome

@leerv. Год назад

Really nice intro to Data Model and why I should probably start using it. My base item set is 150k entries, and there are various dimension tables which can be attached, the numbers get big quickly and generic Excel M.O. starts choking immediately when you throw index(match()) at all of that. Thank you! P.S. it took me far longer to understand those manager names than I would like to admit. :p

@AccessAnalytic Год назад

😄. Glad it helps Lee

@cradleofrelaxation6473 Год назад

Awesome！

@AccessAnalytic Год назад

Thanks 😀

@willykitheka7618 2 года назад

Eish!?!? Excellent presentation, goodness!

@AccessAnalytic 2 года назад

Thanks ( I think ) Willy 😀

@phildanley7285 2 года назад

Thank you so much Wyn for this video. I was able to take a 132 MB report and consolidate it down to just 9 MB! Now everyone in my company who does not have Excel 365 can view it as well as slice and dice.

@AccessAnalytic 2 года назад

Excellent to hear Phil. Thanks for letting me know this was useful.

@BOOMGG-w7d Год назад

@phil danley how did you consolidated it down to 9 MB??

@AccessAnalytic Год назад

The Power Pivot (Data Model) engine performs some amazing compression when columns contain non unique values

@phildanley7285 Год назад

@@BOOMGG-w7d Wyn explained it better than I could. My answer would be "magic" LOL. I don't know how the compression works but it does.

@DeepakKumar-cx5tr 5 месяцев назад

Awesome...

@AccessAnalytic 5 месяцев назад

Cheers 😃

@kebincui 2 года назад

Super👍👍

@AccessAnalytic 2 года назад

😄

@AARSHEYSHAH Год назад

Thanks....

@AccessAnalytic Год назад

You’re welcome

@saleemin27 2 года назад

superb...

@AccessAnalytic 2 года назад

Thank you Saleem

@vijayarjunwadkar 2 года назад

Thanks a ton! Got to see a practical Big Data example handled through Excel and it's amazing! 😊👍👌

@AccessAnalytic 2 года назад

Thanks Vijay

@alparkson5494 2 года назад

So cool, thank you!

@AccessAnalytic 2 года назад

You're welcome Al

@bernielindsey8387 4 месяца назад

Wyn, your content is always helpful and timely. Can you comment here about how to best use the same dataset but doing the analysis in Power BI? Since you don't have a Table, what is the connector with PBI?

@AccessAnalytic 4 месяца назад

Thanks Bernie. I’m not quite sure I follow. You can do it the same way in Power BI by pulling in the folder of CSV files.

@kadichidu4171 Год назад

Please make more videos on excel

@AccessAnalytic Год назад

Here's 28 others 😁 ru-vid.com/group/PLlHDyf8d156Xnoph4CbOiMrqQKiJZ8mhn.

@mkhwald 2 года назад

please, DR. can u explain how to search string in big data before?! thank u. you are a great teacher!

@AccessAnalytic 2 года назад

I don't understand could you provide more details please.

@ivenafaustina2282 2 года назад

This is great and very helpful! Thank you for making this. May I know what PC specification that you used? I want to buy a laptop that can be used to analyze big data.

@AccessAnalytic 2 года назад

Glad it helps Ivena. I'm using an XPS 17 9700 with 32 GB RAM and i7 processesor

@debarshibhattacharjee1326 3 года назад

Thanks for this excellent video. Could you please tell me how you added calendar, location table and cost center table on Queries and Connections section at the beginning of the video? Thanks in Advance

@AccessAnalytic 3 года назад

Hi Debarshi, Those were in an Excel file and I used Get Data from Excel Workbook.

@RobertSklar 2 года назад

I love your video and was able to use every part of it, however, I do not understand how you incorporated the additional tables (cost centers, etc). I get the ability to build the references but how did you get them into the mix?

@AccessAnalytic 2 года назад

Hi Robert, watch this explanation of Data Models and let me know if that helps ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-RV47yX70NN8.html

@CaribouDataScience 2 года назад

How can I export the table created by Power Query to one CSV file?

@AccessAnalytic 2 года назад

Check out this video. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-op6f-3uUFYg.html If you’re using Power BI you can also use bravo.bi ( it’s free ) I’ve a video showing bravo.bi use here at 9:33 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-g4oZ0pOpn-4.html

@TranquilUniverseExploration 2 года назад

Fantastic! Thank you! However I couldn't figure out where consolidation tables came from. Is there a video about that and a link to it???❓❓

@AccessAnalytic 2 года назад

You're welcome Atomic Blue Life

@georgeslehner7733 Год назад

Very informative. I may be a little bit of topics but what software you use for making your video. Thank you!

@AccessAnalytic Год назад

Camtasia and a green screen

@3elperro Год назад

is there a video on how you created the calendar, costcenter, and location table?

@AccessAnalytic Год назад

Here’s a video on the Cslendar table. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-LfKm3ATibpE.html The other 2 tables I just created manually in excel and pulled in using Power Query

@TheLaopi Год назад

Great, how to make thé date table ?

@AccessAnalytic Год назад

Here you go What is a Date Table / Calendar table in Power BI / Excel ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-LfKm3ATibpE.html

@tomharrington1453 2 года назад

Thank you for the video. Very informative. You have an extra zero on the far right in your first graphic "Ten Million rows of data and 100,000,0000".. I think it should be "100,000,000".

@AccessAnalytic 2 года назад

Thanks Tom, well spotted on the mistake. After I’d published it was too late to change 😬.

@murjika 2 года назад

This was excellent! One question please. I have many csv files like in this video, but their columns structure is only slightly different from each other. Some of them have a few extra columns that I don't need. Is it possible to tell the query which columns to take (I can only include the columns that I know exist in all fyles)? Thank you!

@AccessAnalytic 2 года назад

As long as the first file in your folder has all of the columns you need then it won’t matter that other files have extra columns you don’t need. Make sure you use the Choose Columns or Right-Click Remove other columns option

@murjika 2 года назад

@@AccessAnalytic Thank you

@GamerXperienceHD 10 месяцев назад

i just came across this video to understand the capabilities of power query, which has widened my horizon. can I get a download link to the other data for the connections you created before the one of 10 million rows? i want to use that to understand it perfectly if you don't mind.

@AccessAnalytic 10 месяцев назад

I don't have the precise ones but I have extra data sets including the well known AdventureWorks one here under "Dummy Data Sets" accessanalytic.com.au/free-excel-stuff/free-excel-templates/

@tawfiqmohammad575 3 года назад

Hi, thanks to how can export this data file to Power BI or any other tools?

@AccessAnalytic 3 года назад

You could publish the entire file to Power BI , or put the Power Query code into Power BI desktop and export using DAX studio Or copy the M code to a Dataflow There’s no direct way to export power query code currently

@vacilando86 Год назад

If you convert your file type from .XLSX to XLSB, it will be even so much smaller in terms of MB.

@AccessAnalytic Год назад

Actually in this scenario there may be no difference as the Vertipaq engine does an amazing compression job. I just tested on a 5 million row file and there was a no difference (xlsx was actually 1KB smaller)

@warrentso2548 3 года назад

Thanks for your video! What if my data size is far more than that and i need to update it with new data everyday? If I simple refresh the power query, it took me an hour loading time. Is it possible that the power query only add the new data to existing data and do not refresh all of them? Millions thanks!

@AccessAnalytic 3 года назад

Hi Warren, there's no way to do that with Excel currently. What is your data source?

@Lhayang 2 года назад

Is this possible in the older version of Excel?

@AccessAnalytic 2 года назад

Excel 2016 onwards

@gbaires Год назад

If you want to export the 10 MM rows as a csv after transforming the data, is there a way to do it?

@AccessAnalytic Год назад

Yep Export Power Query Tables to CSV using DAX Studio. Even 5 Million records! ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-op6f-3uUFYg.html

@HAT20009 2 месяца назад

How do I get it to use the Exact formula so that it consolidates against a unique code from two sets of databases by also recognising lower case and upper case differences. For example AbC and ABC need to be treated as two unique codes.

@AccessAnalytic 2 месяца назад

I don’t know sorry. The DAX engine encodes ABC and abc the same

@bobbawden7851 Год назад

Great video! Do you have a video that show how to edit the data in massive data sets of up to 10 million rows? I've tried in vain to do it with a million rows using IF formulae (i.e if a record has "x" add the value "y" to field) but excel just gives up under the sheer weight of the formula...

@AccessAnalytic Год назад

Thanks, No video, but it really depends on how many columns you have, what the data source is and how much RAM you have, and if you have Excel 64 Bit. I just tested on 5 million from 10 CSV files in a folder, adding an IF Column value begins with A grab column B else 0. It ran in 30 seconds. 32GB RAM, 64 Bit Excel, 5 columns of data.

@mehedisham Год назад

i am having only the consolidation file...how other files can be inserted in this diagram view?

@AccessAnalytic Год назад

Check out this video ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-RV47yX70NN8.html Essentially click the Get Data button, connect to your other files and Load to... Connection Only... Data Model

@HoangDinhDHA Год назад

How can I get the data for Carlendar, Location and CostCenter?

@AccessAnalytic Год назад

Hi, you can get a Calendar and other datasets here accessanalytic.com.au/free-excel-stuff/free-excel-templates/ I don't have the Location and CostCenter tables available sorry.

@amitchaudhary6 3 года назад

I loved this video. Incredible work. I have a very large CSV file with 1.2 million rows and approx 300 columns. I want to extract only few particular columns from this data. How can it be done? Many thanks in advance.

@AccessAnalytic 3 года назад

Thanks Amit, that should be quite straightforward, use Get Data > From File > From Text/CSV connect to the file, then Ctrl Click on the columns to keep and then right-click REMOVE OTHER COLUMNS, then close and load to Data Model

@amitchaudhary6 3 года назад

Hi thanks for your reply. Is it a good idea to select columns manually if I have around 300 columns. In that case I have to do a lot of scrolling. Any idea if I can extract those desirable column headers name from some other excel or CSV file.. and then keep only these columns in my original large CSV file. Thanks.

@AccessAnalytic 3 года назад

Hi @@amitchaudhary6, yes it's possible but involves writing some M code. However, the Choose Columns button makes it really easy to tick the columns you want to keep / untick the ones you want to remove.

@amitchaudhary6 3 года назад

Thanks for your inputs. It is exactly what I was looking for. 👍

@tutsecret499 Год назад

300 columns?!

@TranquilUniverseExploration 2 года назад

❓Why do errors happen when loading? And what we can do with them?

@AccessAnalytic 2 года назад

Lot's of reasons, different column headings, errors in data. You need to work your way through the Powe Query applied steps. When consolidating multiple files it is difficult to working out which file has the issue.

@aarzoosharma1553 9 месяцев назад

Can i do the same to combine upto 50millions of rows?

@AccessAnalytic 9 месяцев назад

With 64Bit Office and enough RAM on your computer there is no limit The more columns you load and the more unique the rows are then the more computing power is needed

@realfreedom5029 Год назад

So I have a table in MS Access that is close to 4 million records, that I would love exported into excel. Unfortunately excel would only allow a little over 1 million records. Would this work in that instance?

@AccessAnalytic Год назад

You could certainly pull the data into the Excel data model and then create Pivot tables to analyse it

@anandmishra756 9 месяцев назад

So i was using mobile number when loaded it in data model and then pivot 10 didgit number were getting shorter to 5 digit

@AccessAnalytic 9 месяцев назад

Is it loaded as Text data type in Power Query? Which field ( row/col/values) are you putting it in in Pivot

@spaghetticarbonara8066 2 года назад

Is there an easy way to extract data in an Excel data model into a CSV? Thank you!

@AccessAnalytic 2 года назад

How about this? ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-op6f-3uUFYg.html

@rdsvlogventure555 3 года назад

Hi How about text file convert to excel with 2 million rows?

@AccessAnalytic 3 года назад

You can load it to the Excel data model and then create Pivot Table reports

@user-us9hf3lp3f 2 года назад

May I ask your computer spec.(cpu, ram) to handle above 250mb excel files?

@AccessAnalytic 2 года назад

Currently using Dell XPS 32GB RAM i7-10875H CPU @ 2.30GHz (that video was done with Surface Book 16 GB RAM)

@GabrielGonzalez-zj5yh 6 месяцев назад

Hi Teacher, do all csv files contain hearders or only the first csv file should contain headers?

@AccessAnalytic 6 месяцев назад

All contain same headers

@chiragdabhi4929 2 года назад

Need Help: My all CSV data is in same format..in raw header there is unique I'd and in coloum header there is Date(7 days date) and in field are there is different kpi. After load all data I need to add Average and countif >x function at last. I try for average..first take sum of 7 date and divide by 7. got the result but when I use power pivot and use this average data then only count shows error occurred sum is not working on this data.

@AccessAnalytic 2 года назад

I’d recommend posting screenshots and a sample file here aka.ms/excelcommunity

@ArgenisChaffardet 3 года назад

Awesome, I would to ask you a question, if I create the model and everything in Excel 64 bits, my coworkers with Excel 32 bits will have any problem???. Right now with Excel 32 bits an a model I’m having a problem that consume More than 2 GB of RAM, in total with all the tables no more than 50k rows. Thanks

@AccessAnalytic 3 года назад

I wouldn’t rely on it working well on 32 Bit, but it could be ok if they are just slicing and dicing. Newer versions of 32 Bit Excel can now utilise 4GB Ram. 64 bit can utilise all the spare RAM on your machine

@ArgenisChaffardet 3 года назад

@@AccessAnalytic thank you very much for your answer so quickly, it seems I can ask to IT department to change my version to 64 bits but I was a little worried about that and about the macros I developed. Like you wrote, the people will only slice and see the data. It’s important to work on both arquitecture for this particular tool. Again thanks.

@AccessAnalytic 3 года назад

The only real way of knowing is to test it out

@ArgenisChaffardet 3 года назад

@@AccessAnalytic I will do it and I’ll let you know. Thanks

@baldwinyinkore9441 2 года назад

What laptop / specs are you working with?

@AccessAnalytic 2 года назад

That was on my 16GB RAM 64 but office. This one with 24 million rows was 32GB core i7 www.linkedin.com/posts/wynhopkins_excel-datamodel-activity-6902207760197390336-n2Lq

@vikrampurohit8660 2 года назад

Hi, I need to export the filtered data into CSV.

@AccessAnalytic 2 года назад

For more than 1 million records I think you can use DAX Studio to connect to your excel data model and then export to CSV

@vikrampurohit8660 2 года назад

@@AccessAnalytic thanks a lot 🙂

@superfly72 2 года назад

does it have to be CSV? I have a large excel sheet that I need to do this for.

@AccessAnalytic 2 года назад

It can be Excel. Refresh wil take a little longer

@OnlySpy78 2 года назад

If I use Mac, can I do this from my Excel in Mac?

@AccessAnalytic 2 года назад

I don’t believe Power Pivot exists in Mac. Also Power Query is limited

@hassantariq9827 3 года назад

hey after i press okay to add connection only it gives an error after a while that The refresh operation failed because the source data base or the table does not exist, or because you do not have access to the source More Details: OLE DB or ODBC error: The connection could not be refreshed. This problem could occur if the connection was copied from another workbook or the workbook was created in a newer version of Excel.. An error occurred while processing the 'Actuals' table. The current operation was cancelled because another operation in the transaction failed.

@AccessAnalytic 3 года назад

Hi @Haasan Tariq, I think I've seen that error when the source data (Excel file) has N/A# or REF# type errors in it. Are you connecting to an Excel file on your network?

@DiemHoangJenny Год назад

Hi, when I click on 3D Maps, and drag the city/county/date over. Nothing shows up. Why?

@AccessAnalytic Год назад

I don’t know sorry. Country first then city second?

@indianibn-batuta7822 Год назад

Hey! I need your help. I am trying to convert a large XML file into Excel unfortunately whenever I try to convert it I am only getting incomplete data in excel form. e.g Emails are there but the name is missing or vice-versa. Could you please help me out. Thanks in advance

@AccessAnalytic Год назад

I’d recommend posting the issue with screenshots and sample data here aka.ms/excelcommunity

@Quinn0725 Год назад

Is there a way to make the loading of data faster? I am currently handling like 15 millions of data, every week I'm adding like millions of data as well until the end of the year. I'm moving to PowerBI because of this issue, but I wonder if there is a trick on this.

@AccessAnalytic Год назад

Sounds like you need to move to storing your data in a SQL database. A shorter term alternative if you have Power BI is to use Dataflows this video of mine may be useful ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-g4oZ0pOpn-4.html