Data from Table accessed through SQL database

[annerogers]

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

[Anonymous]

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
    Employee

And 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.

[annerogers]

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.

[Anonymous · Accepted solution]

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

[annerogers]

Thank you.  Youa re correct I am not familiar with SQL language.  But I will try using the second one, because I am not sure if with the first method if I will have to do this every time I update the import with new rows of data from the SQL tables. 🙂

[Anonymous]

You will not have to repeat the steps when you refresh your data; when you perform steps in the Query Editor, it creates lines of code used to describe the end-result of what you're looking for; these are persistent across data refreshes. You can see these lines in the "Advanced Editor" in the Query Editor, if you're curious.

So if you remove those columns using the GUI method, with "Remove Other Columns", that change will stick no matter how many times you refresh your data, assuming you're still using that same PBIX file.

Either way, SQL is a fantastic language to know for anyone working with data. I'd recommend you get your feet wet with it anyway, and as a bonus, you will adding a valuable tool to your skillset that expands your job opportunities!

[annerogers]

Thank you so very much for all the information SonnyChilds

[annerogers]

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.