Forum Discussion
Data from Table accessed through SQL database
I am wondering if someone knows the answer to this question.
I have imported data tables from SQL Server. The tables are huge with many columns (100+ per table). I have been told that Power BI prefers tables with fewer columns. A lot of the columns in the tables imported are blank. Is there a way for me to select only those columns with data while still allowing the tables to update with new data when I refresh the connection?
If this is possible, can you show me how to do this?
Thank you, Anne
It sounds like you aren't familiar with SQL; if I were you, I would go with the first method I mentioned above. It'll get you where you need to go without having to learn a new language. In fact, you can do most everything you'd want to do with the Query Editor GUI, rather than writing code.
However, if you want some more information on the SQL approach:
In that SQL code, "Employee" is the table name, and "ID/Name/Date" are all column names. It's a very small SQL statement that basically says:
From the "Employee" table, show me all of the data from the columns "ID", "Name", and "Date".
For more information on how to write some basic SQL, you can go here:
https://docs.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql#examples
7 Replies
You can do this one of two ways.
If you're putting in your database information and then selecting the tables from the Navigator window, then you'll have to remove those columns in your Query Editor. Click the "Edit Queries" button at the top of your page, and then you can begin selecting the columns you want to remove. Once they're selected, you can use the Delete key (or the Remove Columns button) to remove them.
It sounds like you might want to select only the columns you do want, and use the "Remove Other Columns" option.
Alternatively, you can specify exactly what columns you want in raw SQL. When you go to "Get Data" and put in your database information, expand the Advanced Options section and write in a SQL statement that only selects the columns you want. For example, you could have something like this:
SELECT ID ,Name ,Date FROM EmployeeAnd even if the Employee table had 100 other columns with blank data or data you don't care about, you'd only get back those three columns you specified.
thank you, I will give this a try. I assume it will allow the new columns I created in PowerBI in a table to stay in what I had already imported. This is very helpful.
sorry one more question. I assume the words "select" and "from" are commands, right?
Are "ID" and "Employee" supposed to represent the columns or table name; or are the words ",Name" and ",Date" supposed to be the names of the columns? Sorry to be so dense, this is all very new to me.
It sounds like you aren't familiar with SQL; if I were you, I would go with the first method I mentioned above. It'll get you where you need to go without having to learn a new language. In fact, you can do most everything you'd want to do with the Query Editor GUI, rather than writing code.
However, if you want some more information on the SQL approach:
In that SQL code, "Employee" is the table name, and "ID/Name/Date" are all column names. It's a very small SQL statement that basically says:
From the "Employee" table, show me all of the data from the columns "ID", "Name", and "Date".
For more information on how to write some basic SQL, you can go here:
https://docs.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql#examples
