ShowColumns for column type DropDown in a SharePoint list

[NewBee117]

How do I pull only the selected column from a SharePoint list into a collection? ShowColumns can't see the dropdown column type. It said, "the specified column does not exist".  

[Pstork1]

SharePoint doesn't have a column type of dropdown.  There is are several column types that show up as a dropdown in Power Apps.  For example, Choice columns are displayed as dropdowns.  But those are available using ShowColumns().  Are you sure you have the right column name?  If you go to list settings and edit the column you will see the internal column name at the end of the URL.  This is sometimes different than the column display name.  The column name may also be surrounded by quotes.

[NewBee117]

Here is an example, let me know what is the correct column name to use:

1. SharePoint list Dropdown control:

       

2. In the gallery, I am able to pull Yes or No value by using ThisItem.OfficeOpen.Value.

3. ShowColumns gives an error: The specified column "OfficeOpen' does not exist.

     

Thanks!

[Drrickryp · Accepted solution]

@NewBee117 

Sharepoint columns have a display name and an internal name.  These can be confused in PowerApps as ShowColumns() looks for the internal name.  To find the internal name for the column 

You can quickly determine the internal name of a field using the UI:
Open the List Settings page
Under the Columns section, select a column to view the Edit Column page
The URL of this page includes the internal name in the query string. For example, the URL for the Created By field includes the following query string List=%7BF641CEF1%2DCDE2%2D49E1%2D9800%2D861A408EF890%7D&Field=Author. The value for the Field parameter, Author, is the internal name 

Sometimes the display name is the same as the internal name and sometimes it isn't.  Even weirder, the display name works fine in filters but errors out in ShowColumns().  When you get the error that the field doesn't exist, go back to SharePoint and find the internal name and try that. 
In the screenshot below, when I first created my list, SharePoint automatically renamed the Title column to a display name of first_name, as that was the first column in the data I imported.  For a table shaping function like ShowColumns(), I had to go back and find out the internal name to use in the formula.  For more tips on using SharePoint and PowerApps see my post https://powerusers.microsoft.com/t5/Power-Apps-Community-Blog/The-Ten-Commandments-of-SharePoint-in-... .  A more comprehesive explanation of SharePoint and PowerApps is @WarrenBelz 's blog here https://www.practicalpowerapps.com/ .  IMHO, it's a must-read for everyone using the SharePoint connector in PowerApps.

[NewBee117]

@Drrickryp Amazing, the internal name was the fix! Thank you so much.   🙂

My next issue, not sure I should start a new thread or not, JSON() returns data from the ShowColumns collection is not usable, how do I get rid of all the "Value" text?

    

Here is the ClearCollect, is it possible to pick only the value of each column?

     

[Drrickryp]

@NewBee117 

Lets ask @Pstork1 ,  he's much better at that than me. 

[Pstork1]

For complex columns like Choice or Yes/No columns (dropdown columns) there is no way to get rid of that value:.  The column stores an object.  But the object in the column is still usable.  Just like you use ThisItem.OfficeOpen.Value the same syntax will work when accessing a column in the collection.

We could provide a better answer if you would explain why you want it in a collection.  If you are creating a collection to generate JSON and send it to Power Automate then it will work fine with the {value: "item"} syntax in place.  If you are trying to do something else let us know and we'll try to help.

[NewBee117]

@Pstork1 My goal is to export selected columns of the SharePoint list to Excel/csv file for download, within PowerApps. Users do not have the link to the SharePoint site. PowerApps display the data just fine, wish I could export what is showing in the gallery to Excel. I am open to any solution to make this work. 

[Pstork1]

Use JSON() to send the collection to a Flow.  The flow will have a much easier time converting the JSON array into a CSV file anyway.  You can then send the link or the CSV contents back to Power Apps for download.

[NewBee117]

That is what I did, scroll up to see the table created by Flow. Can Flow remove everything other than the actual value of the field? also, need to rename the column header as well.