I was chatting with @hazen35 on my Acumatica Podcast and this happened to come up after we stopped recording. So, we exchanged some emails and he helped me out.

The First Method takes about 30 seconds to refresh.

The Second Method takes about 45 seconds to refresh.

The Hazen Method takes about 20 seconds to refresh!

The Hazen Method is a little more involved, but not too bad.

First, paste this into the Power BI Desktop query window:

let
    Source =
    Json.Document(
        Web.Contents(
            "https://mycompany.acumatica.com/odata/production/PBI-ARInvoiceLines",
			        )
    ),
    value = Source value],
    #"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

Then, click this icon to expand the records into columns.

After that, you can set the names and data types for each column.

So, it’s a little more work, but it allows me to use both the $select and $filter parameters on the OData URL in a way that’s efficient.

Thanks Hazen!

This is actually really good. I can confirm 50% reduction in performance for my test query. OData seems to be very bandwidth intensive as I can see MBs of data downloading with OData, but with JSON it doesn’t even show. 

One thing, Dates need to be modified before attempting to convert to Date type due to JSON datetime format - I just stripped out T00:00:00 from the string and then it converted fine.

Hey @RoyceLithgo!
Glad this works so well! 50% reduction in performance? Do you mean 50% reduction in time / 50% increase in performance? 

As for converting date type, you mean on the $select filter, yes? 
So I have two ways that I do this. I’m I’m entering a static date, I just make sure to use "yyyy-MM-ddTHHss" format. But if I want to use a relative date, I’ll use this template: 
DateTime.ToText(Date.AddDays(DateTime.LocalNow(),-9),"yyyy-MM-ddTHHss")

Replace “-9” with number of days from today you want the relative filter. 
But yeah, you need to make sure to use that format. Took me a lot of trial and error to get that to work, as well!

I’m glad that the Date thing came up. I encountered the same thing after posting this. My workaround in Power Query was simply to change the data type to Text. Then do a second step to change the data type to Date. Just make sure you don’t replace the first step when doing the second step.

Hey @hazen35! Sorry yeah I meant 50% reduction in time taken to refresh. 

For the date, I tried Convert type originally and it gave an error. After removing the timestamp it worked. I didn’t think to try doing something in $select. Might give that a go. 

Thanks for the shoutout @TimRodman ! I also believe that the differences in timing get exponentially or at least progressively worse the bigger the dataset as well. I remember trying the odata.feed with parameters on a generic inquiry with over a hundred thousand rows, and probably 10ish columns and walking away from the machine and having to eventually force quit the refresh. 
The web.contents vastly expands the limitations of the OData feed via Power Query not just because of the ability to select parameters and have the feed filtered on the database side, but also it just seems like the same exact refresh is faster with this method. I haven’t done extensive testing, but would be interesting to understand the differences and limitations. 

@tim I’m sorry for the late response since typically we investigate such cases as support cases.

I measured querying the same Generic Inquiries with and without $select option on 2022R2, and the query with the $select options works almost x2 faster, not slower. I used Developer Tools in Chrome to measure the time. My suspicion is that the slowness is attributed to the client, that’s why The Hazen Method helps.

That would make sense to me. Speaking of the client side of things (good timing), I just tried the refresh using a Dataflow in Power BI and it appears to refresh faster for some reason. I haven’t tried doing any benchmarking yet though.