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. ...
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.

     

  • 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.

       

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

        Thank you so much. That works!