Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Limiting data pulled from D365 FO into Power BI

I've successfully connected, and can pull entire entities into PowerBI, but given the size of our tables this takes a great deal of time.  I'd like to constrain what is pulled, say the last 7 days of orders created.  I've tried the following (along with a few variations on the syntax), but I still receive the entire table (entity) into Power BI.  

 

Am I stuck with Power BI always pulling the entire entity, then I filter out once ALL the data is within Power BI?  (booo!)

 

let
Source = OData.Feed("https://XXXXX.sandbox.operations.dynamics.com/data", null, [Implementation="2.0"]),
SalesOrderLines_table = Source{[Name="SalesOrderLines",Signature="table"]}[Data],
dbo_ShipDate = SalesOrderLines_table{[Schema="dbo", Item="RequestedShippingDate"]}[Data],
#"Filtered Rows" = Table.SelectRows(dbo_ShipDate, each [RequestedShippingDate] > Date.AddDays(DateTime.LocalNow(), -7))
in
SalesOrderLines_table,

  • Anonymous's avatar
    Anonymous
    6 years ago

    I think I solved my own problem.  

     

    Here's the query that worked.

     

    let
    Source = OData.Feed("https://xxxxx.sandbox.operations.dynamics.com/data", null, [Implementation="2.0"]),
    SalesOrderLines_table = Source{[Name="SalesOrderLines",Signature="table"]}[Data],
    #"Filtered Rows" = Table.SelectRows(SalesOrderLines_table, each Date.IsInCurrentQuarter([RequestedShippingDate]))
    in
    #"Filtered Rows"

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    I think I solved my own problem.  

     

    Here's the query that worked.

     

    let
    Source = OData.Feed("https://xxxxx.sandbox.operations.dynamics.com/data", null, [Implementation="2.0"]),
    SalesOrderLines_table = Source{[Name="SalesOrderLines",Signature="table"]}[Data],
    #"Filtered Rows" = Table.SelectRows(SalesOrderLines_table, each Date.IsInCurrentQuarter([RequestedShippingDate]))
    in
    #"Filtered Rows"