Forum Discussion

tomas12344's avatar
tomas12344
Icon for Helper III rankHelper III
2 years ago
Solved

Conditionally invoke function or run statement

Hi All

Customer want to load some data from SAP query, but the problem is, that in this case I have to call several times this query with different filters (basic probelm is that the query in SAP has too many rows and the data cannot be loaded within the 5hours limit of Service, but this is not the question. So we decideded, that the query will be loaded by years).

The question is, if in power query it is posible to excecute  conditionaly some statements or invoke some function. somthing like in C++ (not define a conditional calculation of some column).

Some example:
- have a query A, which contain several columns to load,...

- than I need to invoke this query A and add some filter FA, and second invocation would need to have some filter FB.

- is it posible to define within the query A and condtion, base on some parameter like 

if Parm1=1 then do filter FA

else do filter FB

(FA, FB are some fitering statements written in Power query)

 

I found out, that I can store the query A as function, then invoke it and just add the filter FA or FB. But this would create lot of aditional queries/function, whic is not so optimal for maintanance, as this would need to have defined query A_FA and A_FB,... and call them as separete invocation.

 

  • Hello tomas12344,

    I'm not sure if I understood you correctly. But maybe the following code will help you.

    let
        LoadSAPData = (FilterParameter as text) as table =>
        let
            Source = ... , // YOUR SAP QUERY
            FilteredData = if FilterParameter = "FA" then
                              Table.SelectRows(Source, each [YearColumn] = 2020)
                           else if FilterParameter = "FB" then
                              Table.SelectRows(Source, each [YearColumn] = 2021)
                           else
                              Source
        in
            FilteredData
    in
        LoadSAPData


    Best regards from Germany
    Manuel Bolz


    If this post helped you, please consider Accept as Solution so other members can find it faster.

    🤝Follow me on LinkedIn

2 Replies

  • ManuelBolz's avatar
    ManuelBolz
    Icon for Responsive Resident rankResponsive Resident

    Hello tomas12344,

    I'm not sure if I understood you correctly. But maybe the following code will help you.

    let
        LoadSAPData = (FilterParameter as text) as table =>
        let
            Source = ... , // YOUR SAP QUERY
            FilteredData = if FilterParameter = "FA" then
                              Table.SelectRows(Source, each [YearColumn] = 2020)
                           else if FilterParameter = "FB" then
                              Table.SelectRows(Source, each [YearColumn] = 2021)
                           else
                              Source
        in
            FilteredData
    in
        LoadSAPData


    Best regards from Germany
    Manuel Bolz


    If this post helped you, please consider Accept as Solution so other members can find it faster.

    🤝Follow me on LinkedIn

  • Hi tomas12344,

     

    Using an if ... then ... else function is valid. The key is whether the year filter is pushed to SAP. If query folding works, SAP returns only the selected year. Otherwise, Power BI may retrieve a much larger dataset first.

    For SAP BW, query variables or parameters are usually preferable.

     

    If the same data is available through SAP OData, Power BI Connector for SAP can provide an alternative extraction path: https://www.sap.com/products/data-cloud/partners/metrica-software-inc-power-bi-connector-for-sap.html

    It supports selecting the required entities and fields and applying SAP-side filters. It will not optimize the existing BW query, but it may reduce the transferred data. A 30-day free trial is available.

     

    Documentation:
    https://metricasoftware.com/docs/sap/

     

    Cheers,
    Metrica Team