cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ericonline
Community Champion
Community Champion

Collection to Table?

Hello, 

 

How can I turn a Collection into a Table function? I know a Collection is already a table, but how can I dynamically create a Table function based on the contents of a Collection?

 

Example

1. My Custom Connector requires Table input (thanks @Mike8!)

Table({name: "temp", units: "F"})

2. I have a gallery of potential parameters to pass to the Custom Connetor:

2018-10-18_17-47-34.png

3. Each parameter that is checked gets added to a collection (colSelected)

Collect(colSelected,
        {
         name: ThisItem.name, units: ThisItem.units
        }
    )

How can I parse colSelected and add the values, in the correct format, to the Table function in the Custom Connector?

I've tried doing a lookup for each parameter, but that errors out with "Internal Server error".  I started going down the path of a Switch statement for each parameter ("name" and "units") but that doesn't seem to work either.

Table(
           {name:
                Switch(
                   colSelectedParameters.name,
                   "temp","temp",
                   "feels_like","feels_like"}
                  )
)

Any ideas?

1 ACCEPTED SOLUTION

Accepted Solutions
Mike8
Memorable Member
Memorable Member

Hello,

Can you try instead of     fields:Table({.................}) --> fields:colSelectedParameters..?
It will pass the data from the collection.

View solution in original post

6 REPLIES 6
Mike8
Memorable Member
Memorable Member

Hello ericonline 🙂


Clear(ColSelected);
ForAll(TheGalleryNameThatYouHaveTheCheckedParameters.AllItems,
If(CheckBox1.Value,Collect(ColSelectedParameters,{name:ThisItem.name,units:ThisItem.units}))

It is not completely clear what you are trying to do but if I understood correctly and you are trying to add to a collection all the checked parameters, the formula above can help you.
It iterates on the gallery that you have checked your parameters and if the checkbox is checked (true) it adds the record in the collection.

 

You can also use the filter function..
ClearCollect(colSelectedParameters,
ShowColumns(
Filter(TheGalleryNameThatYouHaveTheCheckedParameters.AllItems,CheckBox1.Value),
"name","units")

If the formulas above don't help, please explain a bit more what you are trying to do and we can find a solution.
)

ericonline
Community Champion
Community Champion

Thanks for the detailed response @Mike8.

 

I'm able to Collect the parameters from the Gallery, using the checkboxes without issue. The place I'm stuck is turning this Collection into the Table function that my Custom Connector requires. 

In our other thread, you helped me hardcode the Table function. I'm now trying to make it dynamic based on what is in colSelectedParameters.

Basically, how do I get from here: 

2018-10-19_8-35-16.png2018-10-19_8-35-35.png

To here, dynamically:
2018-10-19_8-37-28.png

 

Any thoughts on this @Mike8? I also have this issue with an API.

 

Thanks!

Mike8
Memorable Member
Memorable Member

Hello,

Can you try instead of     fields:Table({.................}) --> fields:colSelectedParameters..?
It will pass the data from the collection.

View solution in original post

ericonline
Community Champion
Community Champion

Wow. That was dead simple. A Collection is a Table and can be interchanged with the Table function as long as the columns are the same. 

 

Awesome. I feel a bit silly that I didn't try this... But Awesome, thank you!

Mike8
Memorable Member
Memorable Member

Yes, that's it.
Not silly at all. Finally you would have found the way. We just made it a bit sooner.

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,226)