Select Columns from table instead of removing afterwards in Power Query

0 votes

By default, when importing data from a database table (such as SQL Server) it brings in all columns and then selects which columns to remove.

Is there a way to do the reverse? ie Select which columns you want from a table? Preferably without using a Native SQL solution.

M:

let
    db = Sql.Databases("sqlserver.database.url"){[Name="DatabaseName"]}[Data],
    Sales_vDimCustomer = db{[Schema="Sales",Item="vDimCustomer"]}[Data],
    remove_columns = Table.RemoveColumns(Sales_vDimCustomer,{"Key", "Code","Column1","Column2","Column3","Column4","Column5","Column6","Column7","Column8","Column9","Column10"})
in
    remove_columns 

The snippet above shows the connection and subsequent removal.

Compared to the native SQL way way:

= Sql.Database("sqlserver.database.url", "DatabaseName", [Query="
    SELECT Name,
           Representative,
           Status,
           DateLastModified, 
           UserLastModified,  
           ExtractionDate
    FROM Sales.vDimCustomer
"])

I can't see much documentation on the }[Data], value in the step so was hoping maybe that I could hijack that field to specify which fields from that data.

Any ideas would be well appreciated.

Firstly, I'm concerned that when this gets compiled down to SQL, it gets sent as two queries (as watched in ExpressProfiler).

The first query removes the selected columns and the second selects all columns.

Secondly, it is that if a column is added to or removed from the database then it could crash my report (additional columns in Excel Tables jump your structured table language formulas to the wrong column). This is not a problem using Native SQL as it just won't select the new column and would actually crash if the column was removed which is something I would want to know about.

Oct 31, 2018 in Power BI by Upasana
• 8,620 points
1,859 views

1 answer to this question.

0 votes

Try this.

let db = Sql.Databases("sqlserver.database.url"){[Name="DatabaseName"]}[Data], Sales_vDimCustomer = Table.SelectColumns( (db{[Schema="Sales",Item="vDimCustomer"]}[Data], { "Name", "Representative", "Status", "DateLastModified", "UserLastModified", "ExtractionDate" } ) in Sales_vDimCustomer

This also loads much faster than the other way and only generates one SQL requested instead of two.

answered Oct 31, 2018 by Shubham
• 13,480 points

Related Questions In Power BI

0 votes
3 answers

How to create final table based on Joins of two tables in power BI?

To do so, follow these steps: From the ...READ MORE

answered Dec 16, 2020 in Power BI by Gitika
• 65,930 points
20,536 views
0 votes
1 answer

How do you add a slicer from different table in Power BI?

In order to add a slicer from ...READ MORE

answered Mar 5, 2019 in Power BI by Phalguni
• 1,020 points
648 views
0 votes
1 answer

Calculated column with the sum of values from many columns in a row

Hi, You can create a new column by ...READ MORE

answered Mar 14, 2019 in Power BI by Cherukuri
• 32,990 points
401 views
0 votes
1 answer

How can I get a column value from previous row in Power Query?

Hi Sindhu, add this line as your ...READ MORE

answered Mar 18, 2019 in Power BI by Cherukuri
• 32,990 points
5,645 views
0 votes
1 answer

How to export Power Queries from One Workbook to Another with VBA?

Try solving it using the Workbook. Query ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,200 points
2,341 views
0 votes
1 answer

Excel Power Query: Using List.MatchAny on a column value

try this. let TableA = ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,200 points
2,148 views
0 votes
1 answer

How can I search for multiple strings?

A simple solution is this: List.ContainsAny(Text.SplitAny("This is a test ...READ MORE

answered Oct 24, 2018 in Power BI by Upasana
• 160 points
2,245 views
0 votes
1 answer

Power BI : npgsql installation

As a general rule Npgsql can be ...READ MORE

answered Nov 27, 2018 in Power BI by Shubham
• 13,480 points
3,239 views
0 votes
1 answer

Power Query Web request results in “CR must be followed by LF” Error

What I think is, it might look ...READ MORE

answered Oct 29, 2018 in Power BI by Shubham
• 13,480 points
712 views
0 votes
1 answer

To calculate conditional running total in power query

Try Table Buffer after adding the index, or ...READ MORE

answered Apr 26, 2019 in Power BI by Shubham
• 13,480 points
1,235 views