Forum Discussion

pezwi's avatar
pezwi
Frequent Visitor
6 years ago

Only select certain columns from source

Hi,  I am new to power query and I'm trying to figure out how I can limit the data that I'm extracting from the source because I only need a couple of the columns.  For example, I'm running a web que...
General Comment
DnsLeu's avatar
DnsLeu
Frequent Visitor
2 years ago

Hey pezwi,

I have found a way to directly pull the needed column. I use a OData.feed source. you need to change your table name and the name of the columns you want to add. Then end the command with "as table". See my example code below.

let

Source = OData.Feed("your link", null, [Implementation="2.0"]),
Table = Table.SelectColumns(Source{[Name="Your table name", Signature="table"]}[Data], {"Column 1", "Column 2", "Column 3", "Column 4", "Column 5"}) as table

in
Table

In this way you avoid the extra steps to remove the columns. However, I don't know if this is the fastest way to load data. I have to run some test and see if there is any difference between the run times.

marukosu's avatar
marukosu
Advocate I
2 years ago

Hi, DnsLeu.

I tested your suggestion, and it worked very well to bring only the necessary columns. It is the equivalent of the query's "$select". However, what would the other options look like, such as "$filter" or "$orderby"?

  • DnsLeu's avatar
    DnsLeu
    Frequent Visitor
    2 years ago

    it it possible to add all of those, but they need to be added *before* the column selection. the code that i wrote above serves as the *core* of your data source. so you will need to have that as the origin. I will give an example:

    let
    // Define source
    Source = OData.Feed("your link", null, [Implementation="2.0"]),

    // What to select
    Table = 
          // Sort by 
             Table.Sort(
                  // Select rows

                       Table.SelectRows(
                            // Select columns

                               Table.SelectColumns(Source{[Name="Your table name", Signature="table"]}[Data],
                                       {"Column 1", "Column 2", "Column 3", "Column 4", "Column 5"}

                                ),

                         each [Column 1] = "X"  and [Column 2] = "Y" 

                       ),
              {{"Column 1", Order.Ascending}}

             )

    as table

     

    in 

    Table

     

    I highlighted the source in bold. All of these table.(select) functions require a table as the first argument. this table is given by the highlighted part in the code. this is what i mean as the *origin*. In this way, you select the source with the columns you want, and then apply a cascade of filtering, transformations, etc, without having to repeat the source for each table. function. If you want to add another transformation, you would add it before the Table.Sort. Might not be the most logical way to do it, but it works just fine in my case.