Filtered Excel table to Collection not working

[OurAppGuy]

We have an Excel table that we let users filter down to data they need to see. The results are from 1 to 5 rows from our table. I am able to use drop down and display the results in a List Box using this formula: 

• Sort(Distinct(Filter(HCL,Server in DropServer.Selected.Value , Company in DropCompany.Selected.Value, Controller in DropHBA.Selected.Value),'Versions'),Result)

Which shows properly as:

What I really need to do is use the split function (by comma) and get the data seperated in a single list. So using data from above, I would like to get 5 option from the above list ( 6.5 U1 is duplicated in both lines) in a List Box so that a user can select just the single version they need.

I tried to copy this data to a collection in which I planned to use split function. I used a button with the following OnSelect function:

• ClearCollect(ESXlist,{Ver: Sort(Distinct(Filter(HCL,Server in DropServer.Selected.Value , Company in DropCompany.Selected.Value, Controller in DropHBA.Selected.Value),'Esx Version'),Result) } )

When I try to display the results in a listbox or collection I dont see any data. I get the following:

 I am stuck at this point and not sure how to go further.  Help would be greatly appreciated as I have be at this for a week.

[v-xida-msft]

Hi @OurAppGuy,

Could you please share a bit more about your Excel table?

Do you want to display the data within a Gallery control?

Could you please share a bit more about the Items property of the Gallery control?

Further, where do you use the split function?

I have made a test on my side, please take a try with the following workaround:

Set the OnSelect property of the "Add Collection" button to following formula:

ClearCollect(
      ESXlist,
      {
       Ver:Sort(Distinct(Filter(HCL,Server in Dropdown1.Selected.Value, Company in Dropdown2.Selected.Value, Controller in Dropdown3.Selected.Value),Versions),Result)
      }
)

Set the Items property of the Gallery control to following formula:

Split(
    Left(
       Concat(First(ESXlist).Ver,Result&", "),
       Len(Concat(First(ESXlist).Ver,Result&", "))-2
    ),
    ", "
)

Set the Text property of the Label control within the Gallery control to following formula:

ThisItem.Result

Best regards,

Kris

[OurAppGuy]

To put split in the items worked.

Thanks. You guys are awsome.

[OurAppGuy]

This works some of the time (when there is only 1 result). It show the results of just the first record in Ver. I need to list out all the others. Some times we have 2 or more results.

[v-xida-msft · Accepted solution]

HI @OurAppGuy,

Do you want to list out all records within the ESXlist and then get the data seperated in a single list?

Please take a try to set the Items property of the Gallery control to following formula:

Split(
    Left(
       Concat(ForAll(ESXlist,Concat(Ver,Result&", ")),Value),
       Len(Concat(ForAll(ESXlist,Concat(Ver,Result&", ")),Value))-2
    ),
    ", "
)

Set the Text property of the Label control within the Gallery control to following formula:

ThisItem.Result

Please take a try with above solution, then check if the issue is solved on your side.

Best regards,

Kris