{"id":2117,"date":"2021-01-15T19:10:55","date_gmt":"2021-01-15T18:10:55","guid":{"rendered":"http:\/\/akim.sissaoui.com\/en\/?p=2117"},"modified":"2021-01-15T19:21:20","modified_gmt":"2021-01-15T18:21:20","slug":"populate-a-powerapp-drop-box-with-a-list-of-unique-items-and-no-blank-value","status":"publish","type":"post","link":"https:\/\/akim.sissaoui.com\/en\/informatique\/populate-a-powerapp-drop-box-with-a-list-of-unique-items-and-no-blank-value\/","title":{"rendered":"Populate a Microsoft Power Apps drop down with a list of unique items, and no blank value"},"content":{"rendered":"\n<p>I&#8217;ve been struggling for a while, yet I sorted it out. Wooohooo.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"641\" height=\"451\" src=\"https:\/\/akim.sissaoui.com\/wp-content\/uploads\/2021\/01\/image.png\" alt=\"\" class=\"wp-image-2118\" srcset=\"https:\/\/akim.sissaoui.com\/wp-content\/uploads\/2021\/01\/image.png 641w, https:\/\/akim.sissaoui.com\/wp-content\/uploads\/2021\/01\/image-300x211.png 300w, https:\/\/akim.sissaoui.com\/wp-content\/uploads\/2021\/01\/image-136x96.png 136w\" sizes=\"(max-width: 641px) 100vw, 641px\" \/><\/figure>\n\n\n\n<h4 class=\"wp-block-heading\">The challenge<\/h4>\n\n\n\n<p>I&#8217;ve been trying to populate a drop down list in a Powerapp application. The source is a column called &#8220;Category&#8221; in an Excel document stored on OneDrive. The Category column contains redundant data and empty records. The drop down list has to be populated with single occurrence of categories and but not show an empty line. I want them the list to be alphabetically sorted.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">The solution<\/h4>\n\n\n\n<p>It requires to encapsulate multiple functions. This is the function you have to put in your &#8220;Items&#8221; field for the drop down.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SortByColumns(Distinct(Table1,If(Not(IsBlank(Category)),Category)),\"Result\")<\/code><\/pre>\n\n\n\n<p>Table1 is my source table. Category is my source column (or field). Not(IsBlank(Category)) will collect the full list except blank records. Distinct will then remove duplicates from the list. Finally, SortByColumns will sort the results based on the Category field. By default, sorting will be ascending.<\/p>\n\n\n\n<p>Here we are.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I&#8217;ve been struggling for a while, yet I sorted it out. Wooohooo. The challenge I&#8217;ve been trying to populate a drop down list in a Powerapp application. The source is a column called &#8220;Category&#8221; in an Excel document stored on OneDrive. The Category column contains redundant data and empty records. The drop down list has [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[220],"tags":[275],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/akim.sissaoui.com\/en\/wp-json\/wp\/v2\/posts\/2117"}],"collection":[{"href":"https:\/\/akim.sissaoui.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/akim.sissaoui.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/akim.sissaoui.com\/en\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/akim.sissaoui.com\/en\/wp-json\/wp\/v2\/comments?post=2117"}],"version-history":[{"count":5,"href":"https:\/\/akim.sissaoui.com\/en\/wp-json\/wp\/v2\/posts\/2117\/revisions"}],"predecessor-version":[{"id":2123,"href":"https:\/\/akim.sissaoui.com\/en\/wp-json\/wp\/v2\/posts\/2117\/revisions\/2123"}],"wp:attachment":[{"href":"https:\/\/akim.sissaoui.com\/en\/wp-json\/wp\/v2\/media?parent=2117"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/akim.sissaoui.com\/en\/wp-json\/wp\/v2\/categories?post=2117"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/akim.sissaoui.com\/en\/wp-json\/wp\/v2\/tags?post=2117"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}