none
Excel cell Paramater pass in a ODATA Feed RRS feed

  • Question

  • Hello everyone,

    I try to receive Data from a ODATA feed (D365 in Azure). I would like to filter data through the URL.

    Every thing is working well.

    I can type the filtered value in the URL or With parameters in Powerquery.

    But… I would like to filter by slicer or Value Added in cells.

    The problème is that i have a formula.firewall error (referenc of the request or step and cannot access directly to Database, I need to rebuild)

    As use of paramater are allowed how i can feed paramaters with the cells value ? Or if you have another idea...

    Thanks by advance

    Monday, May 27, 2019 8:22 AM

Answers

  • Hi,

    i found solution :

    i made a table in an excel sheet (named Parameters) and here is my code :

    let
        CriteriaTable=Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],
        #"Type modifié" = Table.TransformColumnTypes(CriteriaTable,{{"Date de début", type date}, {"Date de fin", type date}}),
        AndCriteriaEnvironnement=Record.Field(Table.First(#"Type modifié"), "Environnement"),
        AndCriteriaCompany=Record.Field(Table.First(#"Type modifié"), "Société"),
        AndCriteriaDateStart=Date.ToText(Record.Field(Table.First(#"Type modifié"), "Date de début"),"yyyy-MM-dd"),
        AndCriteriaDateEnd=Date.ToText(Record.Field(Table.First(#"Type modifié"), "Date de fin"), "YYYY-MM-DD"),
        Source = OData.Feed("https://" & AndCriteriaEnvironnement & "/Data/AT_AggDimFocusMonthlyBalanceEntities?$select=Month, MainAccount, Company, CreditAccountingCurrencyAmount, DimensionSet, DebitReportingCurrencyAmount, DebitAccountingCurrencyAmount, CreditReportingCurrencyAmount, PostingLayer, FiscalCalendarPeriodType&$filter=Company eq '" & AndCriteriaCompany &"' and Month ge " & AndCriteriaDateStart & "T00:00:00Z and Month le " & AndCriteriaDateEnd & "T23:59:59Z"),
    in
       Source

    • Marked as answer by Joachim1009 Monday, June 17, 2019 12:21 PM
    Monday, June 17, 2019 12:21 PM

All replies

  • Hi Joachim. Can you share the M formula text of your query? (You can copy it by opening the Advanced Editor.)

    Thanks,
    Ehren

    Wednesday, May 29, 2019 11:36 PM
    Owner
  • Hi,

    i found solution :

    i made a table in an excel sheet (named Parameters) and here is my code :

    let
        CriteriaTable=Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],
        #"Type modifié" = Table.TransformColumnTypes(CriteriaTable,{{"Date de début", type date}, {"Date de fin", type date}}),
        AndCriteriaEnvironnement=Record.Field(Table.First(#"Type modifié"), "Environnement"),
        AndCriteriaCompany=Record.Field(Table.First(#"Type modifié"), "Société"),
        AndCriteriaDateStart=Date.ToText(Record.Field(Table.First(#"Type modifié"), "Date de début"),"yyyy-MM-dd"),
        AndCriteriaDateEnd=Date.ToText(Record.Field(Table.First(#"Type modifié"), "Date de fin"), "YYYY-MM-DD"),
        Source = OData.Feed("https://" & AndCriteriaEnvironnement & "/Data/AT_AggDimFocusMonthlyBalanceEntities?$select=Month, MainAccount, Company, CreditAccountingCurrencyAmount, DimensionSet, DebitReportingCurrencyAmount, DebitAccountingCurrencyAmount, CreditReportingCurrencyAmount, PostingLayer, FiscalCalendarPeriodType&$filter=Company eq '" & AndCriteriaCompany &"' and Month ge " & AndCriteriaDateStart & "T00:00:00Z and Month le " & AndCriteriaDateEnd & "T23:59:59Z"),
    in
       Source

    • Marked as answer by Joachim1009 Monday, June 17, 2019 12:21 PM
    Monday, June 17, 2019 12:21 PM