OData $top in Power BI

Mike Pearson
Mike Pearson Member
in General

Hi,

We are trying to pull a dataset into Power BI and limit to 1000 rows whilst we iterate to make refreshing snappier. However, we're getting an Error 400 that $top isn't supported, though the documentation seems to suggest otherwise. Should this work?

image.png

It feels like the way Power BI structures the query, perhaps its the second row (where you pass which table name you want returned) may needs to pass the limit parameter instead? Below is an approximation of what fails for us:

let

Source = OData.Feed("http://OurApertureURL/odata/default$top1000", null),

SomeTable = Source{[Name="SomeTable",Signature="table"]}[Data]

in

SomeTable


Thanks

Mike

Tagged:

Best Answer

  • Henry Simms
    Henry Simms Administrator
    edited June 19 Answer ✓

    The $top param is applied in OData like this:

    https://local.datastudio/odata/default/Customers_profiled?$top=2

    Where:

    • local.datastudio is my hostname / aperture URL
    • Customers_profiled is the name of the dataset I've published

    In PowerBI, after loading the data I've used PowerQuery in PowerBI to create a FirstN filter

    1. Go to Transform Data (Power Query Editor).
    2. Use the "Keep Rows" option:
      • Keep Top Rows: Keeps only the first N rows.
      • Keep Range of Rows: Keeps a specific range.
      • Filter Rows: Apply conditional filters (e.g., date, category).
    image.png

    The M code:

    let
    Source = OData.Feed("https://local.datastudio/odata/default", null, [Implementation="2.0"]),
    Customers_profiled_table = Source{[Name="Customers_profiled",Signature="table"]}[Data],
    #"Kept First Rows" = Table.FirstN(Customers_profiled_table,5)
    in
    #"Kept First Rows"

Answers

  • Mike Pearson
    Mike Pearson Member

    Thanks Henry. I thought this would retrieve all then filter to the top 1000, but it does seem to limit the actual odata query as we hoped to lighten the load in development phase. Thanks

Categories