Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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.
Hi @pezwi ,
You could add "Query" parameter in Web.Contents() as a filter. Here is a blog for your reference.
@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.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@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
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!