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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
steen_p
Helper II
Helper II

Select columns to load from SQL Server

Hi,

I have now used Power Query for quite some time, to load data into Power BI models as well as SSAS Tabular Models.

I have one annoying issue, that I haven't found a good solution for, and that's how we can load only selected columns into Power Query from e.g. a SQL table of view. When I connect to SQL server and select the table/view, then it per default load all columns. I know that I can then delete the columns in the Editor, but that's quite annoying since it will then cause an error if I at some point in time remove one of those columns in the view. 

I might have a view with 30 columns, but where I only need 10 of them in the model. In that case I remove all of them and that's good for now. Then 6 months later we decide to clean up the view because there are some columns that we know we never used, but if we then delete them, then we'll have to go through all the Tabular models and/or Power BI models that use this view, and delete or alter the "Remove column" step. 

Another issue, is also that if I add a new column to the view, then it will automatically be added to a model if I do a refresh - even though it might not be need it in that particular model. Maybe I don't realize it at that time and then I suddenly end up with some new columns in the Tabular Model or Power BI model, that makes absolutely no sense and where the users suddenly report back what they are meant for.

Is it just us that don't get the point on how to work with this, or is it really so stupid designed? What are you guys doing to prevent the above from happening? In the beginning we also tried to write native SQL queries in Power Query, but we find that really cumbersome and clumsy to work with so that's not really a good option either.

 

Regards

Steen

 

 

 


Steen Schlüter Persson (DK)

1 ACCEPTED SOLUTION
Mariusz
Community Champion
Community Champion

Hi @steen_p 

 

Why not select the column you want to keep and use right-click option to "Remove other columns", or go to  Home > Choose Columns and select your columns there?

 

This will list the columns that you want to keep and not the ones that you don't need.

 

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

View solution in original post

2 REPLIES 2
Mariusz
Community Champion
Community Champion

Hi @steen_p 

 

Why not select the column you want to keep and use right-click option to "Remove other columns", or go to  Home > Choose Columns and select your columns there?

 

This will list the columns that you want to keep and not the ones that you don't need.

 

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

Hi Mariusz,

 

Thanks for the answer. I don't think I've ever thought about that was the way to do it 🤔. Maybe it's because we (so far) do all the table and column work in our Datawarehouse, so we never really use any of the tble "manipulation" options in Power Query. But it seems like it''s doing what we are looking for - in a little ackward way in my opinion though, but that's just how it is.

 

Thanks :-).

 

Regards

Steen 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors