Delete Rows

[AdamDel]

Hi,

I have a large data set with many thousands of rows but I only need about 30 of them. I have removed and filtered out the rows that I don't need in the Power Query Editor but they still "exist", as in any further changes I make to the table takes a long time to process. 

I may be misunderstanding how the queries work but what is the best way to reduce my data set to only include the rows I need? Or is it that all previous steps are being rerun when I make further changes?

Thanks

[LivioLanzo]

Hi @AdamDel ,

what is the source of your data?  If it is a database you can make use of query folding which can drastically speed this up

[AdamDel]

Hi @LivioLanzo ,

Thanks for your reply. The data is actually made up of several Excel files uploaded to SharePoint that are updated regularly, and occasionally new files are added (the layout of these files is a bit of a nightmare). 

Maybe I am going about this in the wrong way. For a bit of background, the Excel files contain two header rows, row one is a constant in all files, row two contains custom names that can be anything and then the data. What I am trying to build up is a mapping table of these custom names, for example:

Example Excel File 1

Name	Custom Milestone 1	Custom Milestone 2	Custom Milestone 3	Custom Milestone 4	Custom Milestone 5	Custom Milestone 6	Custom Milestone 7
Person A	A Milestone	B Milestone	C Milestone	D Milestone	E Milestone	F Milestone	G Milestone
Person A	*data*	*data*	*data*	*data*	*data*	*data*	*data*
Person A	*data*	*data*	*data*	*data*	*data*	*data*	*data*
Person A	*data*	*data*	*data*	*data*	*data*	*data*	*data*
Person A	*data*	*data*	*data*	*data*	*data*	*data*	*data*
Person A	*data*	*data*	*data*	*data*	*data*	*data*	*data*
Person A	*data*	*data*	*data*	*data*	*data*	*data*	*data*

Example Excel File 2

Name	Custom Milestone 1	Custom Milestone 2	Custom Milestone 3	Custom Milestone 4	Custom Milestone 5	Custom Milestone 6	Custom Milestone 7
Person B	Z Milestone	Y Milestone	X Milestone	W Milestone	V Milestone	U Milestone	T Milestone
Person B	*data*	*data*	*data*	*data*	*data*	*data*	*data*
Person B	*data*	*data*	*data*	*data*	*data*	*data*	*data*
Person B	*data*	*data*	*data*	*data*	*data*	*data*	*data*
Person B	*data*	*data*	*data*	*data*	*data*	*data*	*data*
Person B	*data*	*data*	*data*	*data*	*data*	*data*	*data*
Person B	*data*	*data*	*data*	*data*	*data*	*data*	*data*

Target Output

Name	Custom Milestone 1	Custom Milestone 2	Custom Milestone 3	Custom Milestone 4	Custom Milestone 5	Custom Milestone 6	Custom Milestone 7
Person A	A Milestone	B Milestone	C Milestone	D Milestone	E Milestone	F Milestone	G Milestone
Person B	Z Milestone	Y Milestone	X Milestone	W Milestone	V Milestone	U Milestone	T Milestone

At the moment I am basically combining the files and stripping out the unwanted data, any suggestions?

Thanks