@sienna28
You don't need to re-duplicate all the data into a collection - don't waste the memory resources in your App!!
The formula I provided you was for the Item property of your Gallery!
Setting your Items property to that formula will yield the results you want without needing to re-duplicate into a collection!
And...I apologize I was flying fast putting that together in the response...there only needs to be one Ungroup in it. SO, your Items property on your gallery becomes:
Ungroup(
ForAll(<yourTable> As _item,
{ItemRow:
With(_item,
Table({CourseName: CourseName, CourseDate: CourseDate, MaxCandidates: MaxCandidates},
{CourseName: CourseName, CourseDate: CourseDate1, MaxCandidates: MaxCandidates},
{CourseName: CourseName, CourseDate: CourseDate2, MaxCandidates: MaxCandidates},
{CourseName: CourseName, CourseDate: CourseDate3, MaxCandidates: MaxCandidates},
{CourseName: CourseName, CourseDate: CourseDate4, MaxCandidates: MaxCandidates}
)
)
}
),
"ItemRow"
)
More on the above change below...
Ungroup is a function that takes a grouping and normalizes it back into rows.
It is best to look at it from the GroupBy function perspective.
If you have the following data:
TableA
| Name |
Value |
| "AA" |
1 |
| "AA" |
2 |
| "BB" |
10 |
| "BB" |
20 |
| "CC" |
100 |
| "DD" |
200 |
And you were to GroupBy Name. Your formula would be:
GroupBy(TableA, "Name", "_data")
This would result in a table with the following data:
TableB
| Name |
_data |
| "AA" |
|
| "BB" |
|
| "CC" |
|
A record with a column called "Name" that contains text and a column called "_data" that contains a table.
So at this point you can clearly comprehend that doing a Ungroup(TableB, "Name") would result in the original TableA
Now, with the above in mind...if we had a table with just the _data column (a table column) and it looked like this:
TableC
Then if we Ungroup the above - Ungroup(TableC, "_data") then we would have a resulting table of:
(remember this scenario)
So, now taking the formula I provided, it all starts with a record to convert. To get that record we enlist the ForAll function (which is a function that builds tables) to iterate over the source table records.
Then, in that ForAll, we define that our table will have records with a single column called ItemRow
Now, to define the ItemRow, we ungroup a table that we build manually (remember, I mentioned it would have to be transposed manually in the formula).
To do that, we build a table with a common column. In this case it is CourseName. That is all given to a Table function that will return a table with the exact rows in it that we defined (one for each CourseData Column) AND in that, we "normalized" the different course data columns into a common column "CourseDate". So, after this point, those columns don't make a difference any longer as they are now column values in a row in a table.
Now, in the first formula, I had accidentally put in the Ungroup there on the table result...we don't need it as it is already ungrouped.
So...moving on we have just (in the series of iterations over the source data) created the first record of our ForAll table. That record is a record with a column called ItemRow that has a table in it that we defined with the Table function.
After the ForAll has iterated over all the records of the source data, it will be returning a table that look just like TableC above. It has a column with a table in it.
SO...just like above, we then ungroup that by that column.
AND, the result is, a table with all the rows put together (ungrouped).
Hopefully that makes sense and is helpful.
(and ditch the collections...they are way overused!)
