<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Ege Bent on Medium]]></title>
        <description><![CDATA[Stories by Ege Bent on Medium]]></description>
        <link>https://medium.com/@egebent09?source=rss-6644b36a244------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*ZuHDI1P8MHY6IrMZ</url>
            <title>Stories by Ege Bent on Medium</title>
            <link>https://medium.com/@egebent09?source=rss-6644b36a244------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 06:46:23 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@egebent09/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Breaking Free from the 2,000-Row Limit in PowerApps Data Collection]]></title>
            <link>https://medium.com/@egebent09/breaking-free-from-the-2-000-row-limit-in-powerapps-data-collection-3d6007d7fc6c?source=rss-6644b36a244------2</link>
            <guid isPermaLink="false">https://medium.com/p/3d6007d7fc6c</guid>
            <category><![CDATA[power-platform-developers]]></category>
            <category><![CDATA[power-apps-portal]]></category>
            <category><![CDATA[powerapps]]></category>
            <category><![CDATA[microsoft]]></category>
            <category><![CDATA[power-apps-developer]]></category>
            <dc:creator><![CDATA[Ege Bent]]></dc:creator>
            <pubDate>Mon, 08 Apr 2024 12:47:44 GMT</pubDate>
            <atom:updated>2024-04-08T12:47:44.604Z</atom:updated>
            <content:encoded><![CDATA[<p>As many PowerApps developers are well aware, one of the significant challenges they face is the 2,000-row limit when collecting data. This limitation often creates hurdles, particularly when dealing with large datasets, making it difficult to efficiently gather and manipulate the required information within the application.</p><p>However, fear not, as there exists a solution to overcome this obstacle and empower developers to work seamlessly with extensive datasets within PowerApps. By leveraging innovative techniques and strategic approaches, it’s possible to break free from the confines of the 2,000-row limit, opening doors to enhanced functionality and improved user experiences.</p><p>In this guide, we’ll explore a variety of methods and best practices to efficiently manage and collect data beyond the 2,000-row threshold in PowerApps. From optimizing data queries to leveraging external data sources and implementing pagination techniques, we’ll delve into practical strategies that empower developers to effectively handle large volumes of data within their applications.</p><p>Furthermore, we’ll provide step-by-step instructions, code snippets, and real-world examples to illustrate the implementation of these techniques, ensuring that developers have the necessary tools and knowledge to overcome the limitations imposed by the 2,000-row constraint.</p><p>Whether you’re a seasoned PowerApps developer looking to enhance your skills or a newcomer seeking to navigate the complexities of data collection, this guide aims to equip you with the insights and resources needed to transcend the 2,000-row limit and unlock the full potential of your PowerApps applications.</p><p>Join us on this journey as we delve into the realm of data management in PowerApps and discover how to unleash the full power of your applications without limitations.</p><p><strong>Retrieving 2,000+ Rows in PowerApps Using ClearCollect</strong></p><p>The ClearCollect function in PowerApps is a powerful tool for collecting data from a variety of sources, including SharePoint lists, SQL databases, and Excel tables. However, it is well known that PowerApps impose a 2,000-row limit for data retrieval using functions such as <strong>ClearCollect()</strong>. This can be a challenge when dealing with larger data sets.</p><p>To overcome this limitation and retrieve more than 2,000 rows of data using ClearCollect, developers can also use the <strong>ShowColumns()</strong> and <strong>Filter()</strong> functions to fetch the complete columns and apply the necessary filters.</p><blockquote>ClearCollect(collection_dummy,<br>ShowColumns(Filter(dummy_dataset , Row_Id&lt;=2000),”column1&quot;,”column2&quot;,”column3&quot;),<br>ShowColumns(Filter(dummy_dataset, Row_Id&gt;=2001 &amp;&amp; Row_Id&lt;=4000),”column1&quot;,”column2&quot;,”column3&quot;))</blockquote><p>The biggest weakness of this method is that performance decreases as the data size increases. For example, for 100,000 rows of data, data will be drawn in 2,000 rows. This will extend the period considerably. To give an example from my own project: It takes approximately 110–120 seconds.</p><p><strong>Retrieving 2,000+ Rows in PowerApps Using Concurrent and Collect</strong></p><p>The biggest problem for us in the previous method was the performance problem. The reason for this was that while collecting data in 2,000 rows, the code ran sequentially and the next one did not start until the previous 2,000 were completed.</p><p>To overcome this problem, we must ensure that data is collected simultaneously. For this we will use the <strong>Concurrent()</strong> function. This function allows other functions written within it to start simultaneously.</p><p>We prefer the <strong>Collect()</strong> function when writing codes that will start simultaneously. The critical issue here is that each <strong>Collect()</strong> function must work for a different collection. Because it is not allowed to work for the same collection.</p><blockquote>Concurrent(<br>Collect(collection1, ShowColumns(Filter(dummy_dataset, Row_Id&lt;=2000),”column1&quot;,”column2&quot;,”column3&quot;)),<br>Collect(collection2, ShowColumns(Filter(dummy_dataset, Row_Id&gt;=2001 &amp;&amp; Row_Id&lt;=4000),”column1&quot;,”column2&quot;,”column3&quot;)))</blockquote><p>In the next step, we will combine the different collections created into a single collection with <strong>ClearCollect()</strong>.</p><blockquote>ClearCollect(collection_all, collection1, collection2)</blockquote><p>Finally, we can delete the collections we created two steps ago so that they do not take up space.</p><blockquote>Clear(collection1);Clear(collection2)</blockquote><p>To compare these methods, I prepared an example for you with a approximately 20,000-rows dummy data I received from <a href="https://medium.com/u/f9f792defbba">Maven Analytics</a>.</p><p>You can find the codes I used for this comparison and their performance in the following.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*v-NWJsiUN02utjprlSRuNA.png" /><figcaption>Code used in ClearCollect(). The button on the left in the comparison gif at the end.</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*W6rK0v6nu7nMVoz-hKTe4Q.png" /><figcaption>Code used Concurrent() and Collect(). Button on the right in the comparison gif at the end.</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/1*5VaE9uoF1x0Xk647FI88kw.gif" /></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=3d6007d7fc6c" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Removing Unused Measures and Columns in Power BI]]></title>
            <link>https://medium.com/@egebent09/removing-unused-measures-and-columns-in-power-bi-5e15c3db38c1?source=rss-6644b36a244------2</link>
            <guid isPermaLink="false">https://medium.com/p/5e15c3db38c1</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[business-intelligence]]></category>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[bi-tools]]></category>
            <dc:creator><![CDATA[Ege Bent]]></dc:creator>
            <pubDate>Thu, 06 Jul 2023 14:43:05 GMT</pubDate>
            <atom:updated>2023-07-06T14:43:05.728Z</atom:updated>
            <content:encoded><![CDATA[<h3>What is Power BI?</h3><p>Power BI is a business intelligence and data analytics platform developed by Microsoft. It provides powerful tools for users to visualize, report, and analyze data. Businesses and data analysts can use Power BI to extract data from various data sources, process and transform the data, and create a wide range of visual elements, tables, and reports.</p><p>Power BI can connect to different data sources such as Excel, SQL Server, SharePoint, Salesforce, and more. It can retrieve data from these sources, combine and transform the data, and create visually appealing reports and interactive visualizations. Users can share these reports and visual elements, collaborate with others, and have real-time access to updated data.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*UjKGNxS8cC9GfNM_DtpqnA.jpeg" /><figcaption><strong>Power BI interface</strong></figcaption></figure><h3>What is column and measure?</h3><p>Column:<br>In the context of data analysis and reporting, a column refers to a specific field or attribute in a dataset. It represents a single data element within a table or spreadsheet. Each column typically has a unique name and contains values that are related to a specific aspect of the data. For example, in a sales dataset, columns may include “Product Name,” “Quantity Sold,” “Price,” and “Sales Date.”</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/665/1*pGEB1gY5LWv0StfTHnqmSw.jpeg" /><figcaption><strong>Example of column</strong></figcaption></figure><p>Measure:<br>On the other hand, a measure, also known as a metric or a calculated field, is a calculated value or aggregation based on one or more columns in a dataset. Measures are derived from the data in the dataset and provide insights and summaries of the data. They are typically used to perform calculations such as sums, averages, counts, percentages, or other statistical operations. Measures allow users to analyze and compare data based on different calculations or metrics and gain meaningful insights. Examples of measures include “Total Sales,” “Average Revenue per Customer,” or “Percentage of Churned Customers.”<br><em>PS: Measures are created using a formula language called DAX (Data Analysis Expressions). DAX is a formula language used in Power BI for creating measures, calculations, and data manipulation.</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/620/1*zOw8bA1lDEUCa28NlnG88g.jpeg" /><figcaption><strong>Example of measure using DAX</strong></figcaption></figure><h3>What is unused measures?</h3><p>Unused measures refer to measures in Power BI that are not being used in any visuals or calculations within the report. These measures may have been created but are not actively contributing to the analysis or reporting. Identifying and removing unused measures can help improve report performance and reduce clutter in the report’s field list.</p><h3>How can we identify and remove unused measures?</h3><p>Power BI Cleaner and Measure Killer are two popular third-party tools used to automatically detect and remove unused measures and columns from Power BI reports.</p><ol><li>Power BI Cleaner: Power BI Cleaner is a Power BI optimization tool that identifies unused measures, columns, tables, visuals, and other elements in your Power BI report. By analyzing your Power BI report, it guides you in removing unused elements from the report. It also ranks used elements, helping you make your report cleaner and more organized.</li><li>Measure Killer: Measure Killer is a Power BI optimization tool that identifies unused measures and columns in your Power BI report. It analyzes your report and detects unused measures and columns, allowing you to easily identify and remove them from the report. This improves the performance of your report and helps you achieve a cleaner report.</li></ol><p>Both tools are powerful in optimizing your Power BI reports and identifying unused elements. By using these tools, you can effectively manage your Power BI reports and improve report performance by removing unnecessary elements. However, the developer of Power BI Cleaner has announced that they will no longer be actively developing the product and has recommended Measure Killer as an alternative.</p><h3>What does Measure Killer detect?</h3><ul><li>Visuals — including filters applied to visuals, on pages or the whole report.</li><li>Artifacts only used in custom visuals (see compatibility matrix below for details)</li><li>Any kind of measure or relationship</li><li>Columns used exclusively in Power Query, e.g. in joins, appends, references etc.</li><li>Calculated columns</li><li>Calculated tables</li><li>Conditional formatting</li></ul><h3>What does not work?</h3><ul><li>If you copy whole reports and then only do some minor changes, this can lead to false references.</li><li>If you create new queries from existing queries in Power Query it is possible that MK will reference columns (e.g. selected columns / removing columns) that have already been removed in the existing query.</li><li>If a query B (child) references a query A (parent) via e.g. an append, join etc. and those queries have the same column names. If we now have an M reference in one of these columns in query B it will wrongly flag the same column in query A as used.</li><li>DAX expressions like COUNTROWS that only reference a table are not considered unless there is a column or measure referenced as well.</li><li>Calculated columns and calculated tables need to be deleted manually</li><li>Several runs may be necessary to delete all unused columns and measures. (There can be artifacts, that are only referenced in unused measures or columns but are not actually used anywhere in the report.)</li><li>Default titles and subtitles (created by Power BI / default) will not be recognized. If you change the titles in any way they will be detected though.</li></ul><h3>Is Measure Killer free?</h3><p>The free version of Measure Killer provides basic functionality to identify and remove unused measures in your Power BI reports. It allows you to scan your report, view a list of unused measures, and delete them manually.</p><p>The purchase version of Measure Killer offers advanced features and additional capabilities. It provides more detailed insights into the usage of measures, including information about the number of times a measure is used in visuals and the last refresh date of the measure. It also offers the option to automatically remove unused measures with a single click, saving you time and effort.</p><p>The purchase version may also include ongoing updates and support from the developer, ensuring that you have access to the latest features and improvements.</p><p>Overall, the purchase version of Measure Killer provides a more comprehensive and efficient solution for managing and optimizing measures in your Power BI reports.</p><h3>How does Measure Killer work?</h3><ul><li>You need to download setup of Measure Killer. You have more than one options for this. If you don’t want to purchase, you can download free version from the link. <a href="https://en.brunner.bi/measurekiller">https://en.brunner.bi/measurekiller</a></li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/854/1*tqJQjZHEXrtE8G7rkCULQg.png" /></figure><ul><li>Then, install this setup file.</li><li>Run PBI and open your dashboard.</li><li>Run Measure Killer tool from PBI External Tools.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/655/1*NFjqNPjn6UTMPmLpr8pNBg.png" /></figure><ul><li>Choose dataset type of your report.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/499/1*TGcxeRnZ3mn1mJjBrGz7KQ.png" /></figure><ul><li>Choose your dashboard and click Run.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*uisXgxtqpbfWrHNrbYTDAg.png" /></figure><ul><li>Measure Killer detected all unused measures and columns.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*SoXuF7gSg48e6ik_dWh82g.png" /></figure><ul><li>You can click the “Filter Unused” button to see only unused columns and measures. Then, click the Kill measures and columns button and choose which options do you want. I chose kill all unused measures.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*nxB1ySbXKEoR5cGJdo5jqA.png" /></figure><ul><li>After killing all unused measures, tool will ask a question. If you choose “yes”, Measure Killer will run your dashboard again and your removed measures will not seen. You can choose “yes”, every step to check measures and columns I prefer that. Other option is you can choose “Yes” only after end of removing all measures and columns.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/320/1*PMSr2SSmTtFag_pXfZFOrA.png" /></figure><ul><li>As you can see from bellow picture, all unused measures were removed.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*z2xb28aeSt0Cz9Z0DvEZoQ.png" /></figure><ul><li>Choose “Kill columns” for remove unused columns.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Uet_Ls0S8ecDhkpJ20hAIQ.png" /></figure><ul><li>Column removing is based on a table. For this reason, you should select the “remove columns” option above and then select the table name from below.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*z4zBg3AQZ5-ERuEgPlQSBA.png" /></figure><ul><li>Then, click apply. Tool will give a M Code. Copy the M Code.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Q2pDmXW4W4LfFqjANYIJJQ.png" /></figure><ul><li>Now you need to open “Advanced Editor” of selected table from PBI.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2LZYf3pZAhph4vy3-jWmYg.png" /></figure><ul><li>Delete code in the advance editor. And paste your copied M Code. Then, click “done”.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/999/1*MkavEkTN30j5H7CUBBlHYQ.png" /></figure><ul><li>Do this operations for all tables.</li><li>Congratulations, you have removed your unused measures and columns.</li></ul><h3>File size difference in my work:</h3><p>My file size dropped from 38 mb to 9 mb.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/822/1*7OaU2F4REwxAQbJGPYOFmw.jpeg" /></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=5e15c3db38c1" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>