Filtro variabile associato a Target diversi

[Gennaro_64]

Buona sera,

Mi sto esercitando per utilizzare Power Query su casi concreti.

Ipotizziamo di dover gestire una selezione a livello nazionale per la copertura di 18 posti disponibili in tre città: Napoli 5 posti, Milano 7 posti, Roma 6 posti.

Per semplificare ipotizziamo che partecipano 30 candidati che hanno scelto le varie sedi.

Dopo la selezione abbiamo la graduatoria nazionale con i punteggi riportati da ciascun candidato con la posizione e la Città per cui concorre.

Ho creato dunque la tabella della graduatoria nazionale e quella dei posti disponibili.

Le ho importate in entrambe in Power Query e le ho unite relazionandole con la colonna comune.

Ovviamente la “posizione” del candidato nella graduatoria è quella nazionale. Adesso va calata nella singola Città.

Per fare questo ho prima “aggregato” la query per “Città” e, all’interno dell’aggregazione, ho aggiunto un indice che indica la posizione del candidato nella graduatoria locale.

Per fare ciò ho utilizzato la funzione “Table.AddIndexColumn” che ha spiegato Gerardo Zuccalà nella videolezione di cui al link che segue



Ora dispongo di tutte le informazioni per stilare l’elenco dei vincitori associati alle varie Città, ma non conosco le funzioni per:

Inserire un Filtro che mi selezioni (nella colonna indice che ho creato “Posiz_Città”) per ogni Città, un numero di candidati compreso tra 1 ed il numero di “Posti disponibili” per quella Città. Insomma una sorta di Filtro variabile che varia da città a città ed associa il numero dei posti disponibili.

Io ho descritto questo caso, ma potrebbe applicarsi anche ai premi di produzione al personale dei vari Reparti in relazione ai punteggi dei kpi, oppure ai premi per i vari store in base al fatturato e/o proddotti venduti.
E potremmo trovarci di fronte a migliai di record da analizzare. Credo quindi che si possa gestire con Power Query.

Come risolvere?
Allego il file excel.
Grazie e buona serata a tutti.

[Andrea90]

Ciao Gennaro_64,

Io ti propongo un'altra logica che non necessita di un indice.

Crea una formula personalizzata con questo codice (l'ho chiamata "myUdf":

Codice: Seleziona tutto

(myTable as table)=>
let
    OrderTable = Table.Sort(myTable,{{"Punteggio", Order.Descending}}),
    MyFirstN = Table.FirstN(OrderTable,List.First(OrderTable[Posti Disponibili]))
in
    MyFirstN

Sostituisci il codice della tua Graduatoria con questo:

Codice: Seleziona tutto

let
    Origine = Excel.CurrentWorkbook(){[Name="Graduatoria"]}[Content],
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"Posiz_Nazionale", Int64.Type}, {"Nominativo", type text}, {"Città", type text}, {"Punteggio", type number}}),
    #"Merge di query eseguito" = Table.NestedJoin(#"Modificato tipo", {"Città"}, Posti_Disponibili, {"Città"}, "Posti_Disponibili", JoinKind.LeftOuter),
    #"Tabella Posti_Disponibili espansa" = Table.ExpandTableColumn(#"Merge di query eseguito", "Posti_Disponibili", {"Posti Disponibili"}, {"Posti Disponibili"}),
    #"Raggruppate righe" = Table.Group(#"Tabella Posti_Disponibili espansa", {"Città"}, {{"Indice", each _, type table [Posiz_Nazionale=nullable number, Nominativo=nullable text, Città=nullable text, Punteggio=nullable number, Posti Disponibili=nullable number]}}),
    #"Funzione personalizzata richiamata" = Table.AddColumn(#"Raggruppate righe", "Temp", each myUdf([Indice])),
    #"Rimosse colonne" = Table.RemoveColumns(#"Funzione personalizzata richiamata",{"Indice"}),
    #"Tabella Temp espansa" = Table.ExpandTableColumn(#"Rimosse colonne", "Temp", {"Posiz_Nazionale", "Nominativo", "Città", "Punteggio", "Posti Disponibili"}, {"Posiz_Nazionale", "Nominativo", "Città.1", "Punteggio", "Posti Disponibili"})
in
    #"Tabella Temp espansa"

Ti lascio il file di prova.

P.s. Questa soluzione lascia solo le prime N posizioni, pertanto se ci sono 5 posti disponibili, ed il punteggio massimo conseguito in una città è 100, e sono in 6 ad averlo raggiunto, tu alla fine vedrai sempre i primi 5, escludendo il 6° che aveva comunque raggiunto il punteggio max. Se vuoi cambiare questa logica bisogna adattare un attimo le formule, ovvero sostituisci il codice della Udf di prima con questo:

Codice: Seleziona tutto

(myTable as table)=>
let
    MyFilter = Table.SelectRows(myTable, each [Punteggio] >= List.Last(List.MaxN(myTable[Punteggio],[Posti Disponibili])))
in
    MyFilter

Oppure è possibile ottenere lo stesso risultato all'interno di una PowerPivot utilizzando una misura in DAX, senza la necessità di filtrare le righe direttamente dentro PowerQuery.

A presto,
Andrea

[Enrico Galli]

Ciao. Oltre a ciò che ha detto Andrea, c'è una complicazione in più: se in una città sono disponibili 5 posti ma si sono presentati 4 candidati, il posto vacante viene riassegnato a un candidato inizialmente escluso in un'altra sede? Secondo quale criterio?

[Gennaro_64]

Andrea90
Grazie per la risposta.
Per me complessa. La devo studiare ....

Enrico Galli
Nel caso di cui all'esercitazione, se il numero dei candidati idonei per quella città è minore del numero dei posti disponibili, non tutti i posti verranno copertii.

Ho parlato di candidati idonei solo adesso perché ho imparato il metodo in PQ per "filtrarli". Avrei applicato un filtro al punteggio e conservato solo le righe/candidati con valori superiori ad una certa soglia.

L'altro argomento che dovrò affrontare, ma volevo andare per gradi, lo anticipo, è capire quale è il punteggio min e max per poter rientrare tra i vincitori nelle varie città.

Insomma, andare per gradi, un passo alla volta.

La BI credo sia un mondo interessante. E credo che oggi sia una nicchia di una nicchia.
Per la diffusione io vedo due scuole di pensiero: quella più orientata al codice, riduzione di passaggi, nidificazione delle formule, etc. e quella più orientata ai concetti, passo passo, che utilizza al massimo gli strumenti offerti dall'interfaccia utente e presenti a video, poco attenta alla numerosità dei passaggi.

La BI ha reclutato tanti adepti tra le fila di chi in passato non ha mai visto un codice vba. Ha reso accessibile a tanti come me un mondo, prima appannaggio di esperti.

La diffusione della BI, a mio avviso, dipende molto dalle scuole di pensiero di cui sopra.
Io, lo avrete intuito, preferisco la seconda ....
Alla prossima.