Limitation of loaded columns

[Anonymous]

Hi all,

I'm struggling how to limit number of columns I'm downloading from an excel file. By mistake, excel I need to use has maximum number of columns (~16k), which makes my query unable to load. Therefore, I would like to load only 10 first columns which I need. I was trying to enter it manually in Advanced Editor in M, but couldn't succed. 

Could anyone help me, what should I include, to force loading only first columns, BEFORE whole excel sheet is loaded? Thanks!

I'm attaching screen, of how query looks like.

[Vijay_A_Verma · Accepted solution]

1. Excel file connector will not offer this flexibility. You will have to import all the columns which you are not able to do.

You will need to use something which offers this flexibility. Many sources offer this and in my view SQL server is a good option as SQL Server Express is a free download to be used on a Desktop.

You will need to import your Excel data in SQL Server and then when you import from SQL Server, then you can put a SQL statement there to indicate which all columns you want to pull. 

2. I am thinking of another solution. Why not write a macro which pulls data from your Excel file into another Excel file with required columns only. Then you can pull data through Power Query from this intermediate Excel file. 

If you want this solution let me know, I can write a macro for you. You just need to give me a sample excel file with 1 to 2 rows of data without any confidential/sensitive data and also let me know which all columns you need in PQ.

[Anonymous]

Thanks for your reply, I thought that maybe it will be possible somehow. 

Well, thank you for you offer, it's really kind of you, but I believe it will be easier, to simply ask person responsible for this file, to adjust it in correct way 🙂