Power Platform Community Forum Thread Details

Dynamic columns and 2 columns working "together"

Hello,
first sorry for my english and the description, I don't find the good words.
I want to help my music teacher. Twice a year, he organize a concert where the student are playing guitar.
It's very difficult, for him to choice who play this or this song.
Some songs can be played 2 time.
The students are playing only 3 songs.

The joinded file contains an example, with the first table is the wish of the students (student are telling from 1 - prefered song - to the end - worst song - (in the attached file : 11 songs) the order of preference.
and the final table contains the final choice of the teacher.
In the real file, there is 10 guitarists, I put only 4 in the exemple.
To obtain the final choice, i'd like to make suggestions (with Power Query) for the teacher to help.

And then, he can finalize, modify manually the table.

The number and the name of students are different at each concert. PQ must be dynamic fot this 2 points.

Other, in the final table, for each student, i want to have 2 columns (their wish (1 to 11) and the final choice). The final choice, is first fill by PQ for suggestion. and after fill manually by teacher for final setlist.
For this gestion of 2 columns and dynamic gestion, i'm blocked with, I don't find the way to make PQ.

Some suggestions (choice of song) i want to set with PQ :
- for each song, If nb student (having wish <= 4) is >= 4, the song will be played 2 time (2 guitar at each). I made it with a combination of formula and PQ, but i'd like to do this with PQ only.
- if song played 1 time and nb student (having wish <= 4) = 2, they are the guitarist for the song, i wan't to set "X" to the choice / suggestion for this 2 guitarists for this song. (for example, in my file, for the line R Cray", Ted Song and Eric song must be set to "X". For "Under T B", Phil song and Franck song set to "X".
and other suggestion like that...

Thank you for help me to tell me the good way to do this. I 've already made PQ and modify it with M Language, but nothing so complicated.

Quick Links

Leaderboard > Power Apps - Power Query