Only select certain columns from source

[pezwi]

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.

[v-eachen-msft]

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

[Greg_Deckler]

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

[pezwi]

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]]
),

[mahoneypat]

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

[amitchandak]

@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