cancel
Showing results for 
Search instead for 
Did you mean: 
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.

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


@ 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!:
Mastering Power BI 2nd Edition

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
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors