Forum Discussion

magic-powerbi's avatar
magic-powerbi
Frequent Visitor
8 years ago
Solved

Get Limited data in Power BI

Hi,

I am getting data through an ODBC Connection. The table I am trying to import has 30 million rows. The entire data can't be imported as it throws error.

 

I have a date column in the table. I am not able to apply filter on it as it is treated as text by Power Bi though it is a datetime datatype in the IBM DB2

If I convert it to date/time and then apply filter, it still get the entire data before applyng filter.  I am not sure how to navigate this situation.

 

Appreciate the help

 

Need Help
  • stretcharm's avatar
    stretcharm
    8 years ago

    You can change the source from import without breaking you reports as long as the name and column list stay the same.

     

    This is an example odbc source from the advanced editor

     

    let
    Source = Odbc.DataSource("dsn=WideWorldImporters", [HierarchicalNavigation=true]),
    WideWorldImporters_Database = Source{[Name="WideWorldImporters",Kind="Database"]}[Data],
    Website_Schema = WideWorldImporters_Database{[Name="Website",Kind="Schema"]}[Data],
    Customers_View = Website_Schema{[Name="Customers",Kind="View"]}[Data]
in
    Customers_View

    You can change it manually in the advanced editor to something like this. Changing the query to limit to relevant rows.

    let
        Source = Odbc.Query("dsn=WideWorldImporters", "SELECT TOP (1000) [CustomerID]#(lf)      ,[CustomerName]#(lf)      ,[CustomerCategoryName]#(lf)      ,[PrimaryContact]#(lf)      ,[AlternateContact]#(lf)      ,[PhoneNumber]#(lf)      ,[FaxNumber]#(lf)      ,[BuyingGroupName]#(lf)      ,[WebsiteURL]#(lf)      ,[DeliveryMethod]#(lf)      ,[CityName]#(lf)      ,[DeliveryLocation]#(lf)      ,[DeliveryRun]#(lf)      ,[RunPosition]#(lf)  FROM [WideWorldImporters].[Website].[Customers]#(lf)  order by [CustomerID] desc")
    in
        Source

    You can click the settings cog icon next to Source and use the advanced options to add the select and finally delete the navigation step.

     

    If you have 30million rows you may want to aggregate the data and limit the columns in the select and fix the report if this changes things. It's not going to be the best performing powerbi doc if you have to load that many rows on every refresh.

     

6 Replies

    • magic-powerbi's avatar
      magic-powerbi
      Frequent Visitor
      8 years ago

      Hi,

      I appreciate the response.  I tried doing that and it works if I am doing for a new report.

       

      But unfortunately, I already have an existing report in Power BI and it does not use the SQL to get the rows rather it uses the table directly. And I am not sure how can I change this existing connection to use the SQL so that I can get the current data and still have my reports working. 

       

      Thank you!

      • stretcharm's avatar
        stretcharm
        Icon for Memorable Member rankMemorable Member
        8 years ago

        You can change the source from import without breaking you reports as long as the name and column list stay the same.

         

        This is an example odbc source from the advanced editor

         

        let
    Source = Odbc.DataSource("dsn=WideWorldImporters", [HierarchicalNavigation=true]),
    WideWorldImporters_Database = Source{[Name="WideWorldImporters",Kind="Database"]}[Data],
    Website_Schema = WideWorldImporters_Database{[Name="Website",Kind="Schema"]}[Data],
    Customers_View = Website_Schema{[Name="Customers",Kind="View"]}[Data]
in
    Customers_View

        You can change it manually in the advanced editor to something like this. Changing the query to limit to relevant rows.

        let
            Source = Odbc.Query("dsn=WideWorldImporters", "SELECT TOP (1000) [CustomerID]#(lf)      ,[CustomerName]#(lf)      ,[CustomerCategoryName]#(lf)      ,[PrimaryContact]#(lf)      ,[AlternateContact]#(lf)      ,[PhoneNumber]#(lf)      ,[FaxNumber]#(lf)      ,[BuyingGroupName]#(lf)      ,[WebsiteURL]#(lf)      ,[DeliveryMethod]#(lf)      ,[CityName]#(lf)      ,[DeliveryLocation]#(lf)      ,[DeliveryRun]#(lf)      ,[RunPosition]#(lf)  FROM [WideWorldImporters].[Website].[Customers]#(lf)  order by [CustomerID] desc")
        in
            Source

        You can click the settings cog icon next to Source and use the advanced options to add the select and finally delete the navigation step.

         

        If you have 30million rows you may want to aggregate the data and limit the columns in the select and fix the report if this changes things. It's not going to be the best performing powerbi doc if you have to load that many rows on every refresh.