Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Limitation of loaded columns

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.

 

Bercikk_0-1651744681987.png

 

 

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

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.

View solution in original post

2 REPLIES 2
Vijay_A_Verma
Super User
Super User

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
Not applicable

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 🙂

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors