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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
pezwi
Frequent Visitor

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 query to an api and it returns the list of records and each record has a timestamp, loglevel, hostid, text and then 22 custom fields that is extracted from the text field.  I'm just interested in grabbing the data from a couple of the custom fields.  Currently it downloads all the data for record and then I have it remove the extra columns.  The issue that I'm facing is because all of the data is downloaded it takes a very long time to complete.  Is there a way to filter out via the source query instead of downloading all the data and removing the other columns?

 

Thank you very much for your time and assistance.

8 REPLIES 8
DnsLeu
Frequent Visitor

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.

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"?

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.

v-eachen-msft
Community Support
Community Support

Hi @pezwi ,

 

You could add "Query" parameter in Web.Contents() as a filter. Here is a blog for your reference.

https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-i...

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
Greg_Deckler
Super User
Super User

@pezwi - Well, if this was a SQL source you could use a View or write a SQL statement but since it is a Web API you are going to be limited to the functionality of that Web API. For example, I believe ODATA allows you to specify columns/fields but not sure about just any old Web API. 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

I'll have to look into the Odata option.  I'm not finding a lot of examples for that.

 

Currently I'm pulling the data into a list which is capturing all the records.  It would be great if I could do loop through the records but I'm having trouble figuring that out.

 

Source = List.Generate(
() => getPage(""),
each _[scrollToken] <> null,
each getPage(_[scrollToken]),
each [scrollToken = _[scrollToken], records = _[records]]
),

Here is an article I wrote last year that may help you get started with using OData.  It includes examples of adding $select and other ways to let you limit the initial amount of data returned. 

 

https://powerpivotpro.com/2019/02/some-odata-tips/

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


amitchandak
Super User
Super User

@pezwi , Right click on Table and Advance Query and see if there is a list of columns for selection. if yes, then try to remove some of them and try

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.