Forum Discussion

j0710's avatar
j0710
Frequent Visitor
2 years ago

How to Export More Than 1000 Rows from a Power BI Report to a CSV File in Power Automate?

Question:
I'm currently working on a Power Automate flow where I need to export data from a Power BI report to a CSV file.
However, I'm encountering a limitation where only 1000 rows of data are being exported. Is there a way to export more than 1000 rows from a Power BI report into a CSV file using Power Automate?
Details:
I have a Power BI report that contains more than 1000 rows of data.
I've set up a Power Automate flow to export data from this report to a CSV file.
However, I'm only able to export 1000 rows of data at a time.
Is there a workaround or configuration setting that allows me to export more than 1000 rows from the Power BI report?
Additional Information:
I've checked the export settings in Power BI but couldn't find any options to increase the row limit for CSV exports.
I'm open to using any alternative methods or connectors if needed to achieve this requirement.
Any guidance or suggestions would be greatly appreciated!
please help to solve : (
screenshot of flow in power automate connected through PowerBI

 

 

admin
capacities
power bi
Real-time Analytics

3 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    Consider using "Run a Query against a Dataset" instead, and use your Power Automate visual only to convey the filters.

    • j0710's avatar
      j0710
      Frequent Visitor
      2 years ago
      I have created a table chart using a field parameter with more than 1000 rows in Power BI. I am using the "Run a Query against a Dataset" action in Power Automate, and I need assistance in modifying the DAX query.
       
      Here is the DAX query I am currently using:
      -------------------------------------------------------------------------------
      // DAX Query
      DEFINE
      VAR __DS0Core = 
      SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL(
      ROLLUPGROUP(
      Table_Name[PolicyTypeCode],
      Table_Name[Channel],
      Table_Name[Executive Code],
      Table_Name[Executive Name],
      Table_Name[BrokerExecCode],
      Table_Name[BrokerExectiveName],
      'GIM_Branch'[Branch Name],
      Table_Name[Motor & Non Motor],
      Table_Name[PolicyTypeDesc],
      'Vehicle_Info'[PrdClassCode],
      'Vehicle_Info'[PrdClassDesc]
      ), "IsGrandTotalRowTotal"
      ),
      "count_NOP", Table_Name[count NOP],
      "Countpkpoliid", IGNORE(
      CALCULATE(COUNTA(Table_Name[pkpoliid]))
      )
      )
       
      VAR __DS0PrimaryWindowed = 
      TOPN(
      502,
      __DS0Core,
      [IsGrandTotalRowTotal],
      0,
      Table_Name[PolicyTypeCode],
      1,
      Table_Name[Channel],
      1,
      Table_Name[Executive Code],
      1,
      Table_Name[Executive Name],
      1,
      Table_Name[BrokerExecCode],
      1,
      Table_Name[BrokerExectiveName],
      1,
      'GIM_Branch'[Branch Name],
      1,
      Table_Name[Motor & Non Motor],
      1,
      Table_Name[PolicyTypeDesc],
      1,
      'Vehicle_Info'[PrdClassCode],
      1,
      'Vehicle_Info'[PrdClassDesc],
      1
      )
       
      VAR __DS0CoreNoInstanceFiltersNoTotals = 
      FILTER(KEEPFILTERS(__DS0Core), [IsGrandTotalRowTotal] = FALSE)
       
      EVALUATE
      GROUPBY(
      __DS0CoreNoInstanceFiltersNoTotals,
      "Mincount_NOP", MINX(CURRENTGROUP(), [count_NOP]),
      "Maxcount_NOP", MAXX(CURRENTGROUP(), [count_NOP]),
      "MinCountpkpoliid", MINX(CURRENTGROUP(), [Countpkpoliid]),
      "MaxCountpkpoliid", MAXX(CURRENTGROUP(), [Countpkpoliid])
      )
       
      EVALUATE
      __DS0PrimaryWindowed
       
      ORDER BY
      [IsGrandTotalRowTotal] DESC,
      Table_Name[PolicyTypeCode],
      Table_Name[Channel],
      Table_Name[Executive Code],
      Table_Name[Executive Name],
      Table_Name[BrokerExecCode],
      Table_Name[BrokerExectiveName],
      'GIM_Branch'[Branch Name],
      Table_Name[Motor & Non Motor],
      Table_Name[PolicyTypeDesc],
      'Vehicle_Info'[PrdClassCode],
      'Vehicle_Info'[PrdClassDesc]
       
       
      // DAX Query 
      DEFINE
      VAR __DS0Core = 
      SUMMARIZE(
      'FIELD_PARAMETER',
      'FIELD_PARAMETER'[FIELD_PARAMETER Fields],
      'FIELD_PARAMETER'[FIELD_PARAMETER Order],
      'FIELD_PARAMETER'[FIELD_PARAMETER]
      )
       
      VAR __DS0BodyLimited = 
      TOPN(
      152,
      __DS0Core,
      'FIELD_PARAMETER'[FIELD_PARAMETER Order],
      1,
      'FIELD_PARAMETER'[FIELD_PARAMETER Fields],
      1,
      'FIELD_PARAMETER'[FIELD_PARAMETER],
      1
      )
       
      EVALUATE
      __DS0BodyLimited
       
      ORDER BY
      'FIELD_PARAMETER'[FIELD_PARAMETER Order],
      'FIELD_PARAMETER'[FIELD_PARAMETER Fields],
      'FIELD_PARAMETER'[FIELD_PARAMETER]
      -------------------------------------------------------------------------------
       
      I would appreciate any guidance or suggestions on how to modify this query to improve performance and handle large datasets efficiently.
       
      Thank you in advance for your help!
      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        2 years ago

        Change your table visual to its simplest form.  Only the columns you need, no row totals.  Then the DAX query will be much simpler.