Forum Discussion

ouafaa's avatar
ouafaa
Icon for Helper II rankHelper II
8 years ago

Pre-Filtering Data before Importing

we have an sql server database that contains very old data, and we just need some recent data for creating our reports. so, we chose to import only the data whose date is later than a date X for exem...
Need Help
ouafaa's avatar
ouafaa
Icon for Helper II rankHelper II
8 years ago

Hi, 

 

thanks for your replies.

 

when we do et Data->advanced options and write some SQL statements to filter data, can we filter many tables on the same time ? can we write an sql statement that retrieve many tables and filter them befor importing data to power bi ?

 

Thanks

 

 

v-yuta-msft's avatar
v-yuta-msft
Icon for Community Support rankCommunity Support
8 years ago

Hi ouafaa,

 

Yes, you can. But keep in mind. Power bi only support sql statement like select .. from ... where ... , don't spport stored procedure.

 

Hope this can help you.

Jimmy Tao

  • ouafaa's avatar
    ouafaa
    Icon for Helper II rankHelper II
    8 years ago

    Hi v-yuta-msft,

     

    Thanks for your reply, but I still don't know how I can do this : filter many sql server tables using advanced options sql statement befor loading those tables in different tables in power bi 

     

    Could you give me an exemple of this sql statement please?

     

    Thanks a lot for your help

     

    Best regards,

    Ouafaa

    • cnschulz's avatar
      cnschulz
      Icon for Helper IV rankHelper IV
      8 years ago

      I would also like to do this. We have work orders that are linked to addresses. Im inporting two tables, workorder (for a date range) and address. The address table contains *all* possible addresses (> 2million rows) and I only want to import the ones linked to work orders for the date period selected.

       

      Any help appreciated.

      • _seanrich_'s avatar
        _seanrich_
        Frequent Visitor
        2 years ago

        For future readers, you would solve this in one of two ways. If you want two tables (data sources) in your Power BI file, you'd create two separate data sources using Get Data > SQL Server database. However, if you wanted a single data source in Power BI with both workorder and address records, you'd write SQL in the aforementioned "Advanced options" when creating your data source. Your SQL might look something like:

        select

           workorder.WorkOrderId,

           workorder.WorkOrderDate,

          address.AddressId,

          address.AddressData

        from workorder

           left join address on address.AddressId = workorder.AddressId

        where workorder.WorkOrderDate >= [someStartDate]

           and workorder.WorkOrderDate <= [someEndDate]

         

        Having some basic SQL query knowledge will go a long way in solving these problems.