EXCEL AND big data

New Contributor
How do i use Excel to analyse data. I have approx 3000 rows of data to be compared to approx 1.6 million row of data and extract data baaed on a certain column.
6 Replies

@Nrpandey00 You need Power query for this. So, if you are on a Windows version of Excel (2010 or later)  you can use it. More about Power Query in the link below.

https://powerquery.microsoft.com/en-us/excel/ 

Hi @Nrpandey00 

 

As @Riny_van_Eekelen mentioned you need Power Query, but also likely Power Pivot

 

Id suggest you use Power Query to load the 2 tables of data into Power Pivot ( " The Data Model" ) and then create a relationship between them in the Diagram view  (there needs to be a common field in both data sets for this to work, and ideally one table has a unique item in each row of that common field.

 

You can then load your results into a Pivot Table

 

 

Thanks Ven. I used power query to load the data . Then when i tried to add/create a helper column to add formula, my excel crashes every single time.

I am using the latest version of Excel.

Is there any workaround this issue??
Thanks Wyn. As i mentioned it to Van, I wanted to add an extra columns to help create a unique ID. But everytime i try to put formula using a custom column, my excel crashes.

I am using the latest version of excel and my laptop is also pretty new. Is it a hardware constraints ?

Hi @Nrpandey00 

 

Are you adding a column to the million row + table?

 

Where is your data being pulled from?

 

Ideally you want to be using 64 Bit Excel and have at least 8GB of RAM

 

If the problem continues you could load the data to the data model and add a calculated column there.

@Nrpandey00 

Perhaps I missed something, I understood initial tasks as filter one table based on values in another table column. Or okay, take values only from one or few columns of the first table, doesn't matter.

 

If so, why do you need any formulas and even relationships? Perhaps you may forget for a while about the size of the source and provide small sample which illustrates what shall be done.

 

So far I see that as DAX expression

evaluate
FILTER(Table1,
 Table1[A] IN VALUES (Table2[A])
)