Powerquery - arrotondare secondo un criterio definito

Non è propriamente un programma di BI, ma nelle sue versioni più recenti anche Excel può fare grandi cose!

Moderatore: Utilizzo_prof_Excel

Rispondi

Astaga
Messaggi: 4 | Topic creati
Iscritto il: ven 8 ott 2021, 13:19

Powerquery - arrotondare secondo un criterio definito

Messaggio da Astaga »

esempio dati x arrotondamento.xlsx
esempio del problema di arrotondamento
(27.76 KiB) Scaricato 15 volte
Buongiorno a tutti.

Avrei la necessità di arrotondare dei valori (tabella 1 colonna punteggio), non con regole matematiche, ma secondo i valori definiti in tabella 2 e riportare il risultato nella colonna di tabella 1 Punteggio_arrotondato).

Con excel grazie alla funxione CERCA.X con alcuni passaggi (illustrati nel file allegato) è possibile arrivare alla soluzione (colonna punteggio arrotondato); il mio problema è che dovrei ottenere lo stesso risultato con POWERQUERY e non so proprio da dove cominciare.

Qualcuno riesce a darmi la la soluzione o anche una indicazione su come impostare tale problema (ammesso che sia possibile)?

un grazie a tutti per l'attenzione


Avatar utente

Andrea90
Messaggi: 2193 | Topic creati
Iscritto il: dom 28 giu 2020, 19:41
Luogo: Bologna
Ringraziato: 666 volte
Contatta:

Powerquery - arrotondare secondo un criterio definito

Messaggio da Andrea90 »

Ciao Astaga,

Ti allego un file con una prova che ho fatto.
Il codice in M è il seguente e serve per trovare il valore precedente e successivo nella matrice (il resto dovrebbe essere facile):

Codice: Seleziona tutto

let
    Origine = Excel.CurrentWorkbook(){[Name="Original"]}[Content],
    ApproxList = List.Buffer(Excel.CurrentWorkbook(){[Name="Criteri"]}[Content][#"Criterio#(lf)arrotondamento"]),
    ValueAfter = Table.AddColumn(Origine, "ValueAfter", each ApproxList{List.PositionOf(List.Transform(ApproxList, (x)=> if x >= [Punteggio] then "ok" else null), "ok")}),
    ValueBefore = Table.AddColumn(ValueAfter, "ValueBefore", each ApproxList{List.PositionOf(List.Transform(ApproxList, (x)=> if x <= [Punteggio] then "ok" else null), "ok", Occurrence.Last)}),
    #"Modificato tipo" = Table.TransformColumnTypes(ValueBefore,{{"Codice", type text}, {"Punteggio", type number}, {"ValueAfter", type number}, {"ValueBefore", Int64.Type}})
in
    #"Modificato tipo"
Andrea
Allegati
ValueBeforeAfter.xlsx
(18.52 KiB) Scaricato 12 volte
Se hai gradito l'aiuto che hai ricevuto considera di contribuire alle spese per il mantenimento del forum facendo una libera DONAZIONE --> Link

Ricordarsi di segnare come "RISOLTE" le discussioni per le quali si è ricevuto un feedback positivo. Per vedere come fare --> Link

Autore del topic
Astaga
Messaggi: 4 | Topic creati
Iscritto il: ven 8 ott 2021, 13:19

Powerquery - arrotondare secondo un criterio definito

Messaggio da Astaga »

Grandissimo!!!!! E grazie anche per la velocità della risposta! Ho replicato le istruzioni, per vedere se le avevo comprese, anche a tabelle con nomi differenti ed è tutto ok. Mi hai risolto un grande problema.
Ora sto studiando le sintassi che hai utilizzato....... certo che ricostruire il funzionamennto di:
"ApproxList{List.PositionOf(List.Transform(ApproxList, (x)=> if x >= [Punteggio] then "ok" else null), "ok")}" non sarà facile :)

Grazie ancora Andrea
Avatar utente

Gian Jo
Messaggi: 16 | Topic creati
Iscritto il: mar 14 set 2021, 13:52
Ringraziato: 3 volte

Powerquery - arrotondare secondo un criterio definito

Messaggio da Gian Jo »

Ciao a tutti,
Ho provato anche io per esercizio.
Buona serata!!

Codice: Seleziona tutto

let
    Origine = Excel.CurrentWorkbook(){[Name="Tabella1"]}[Content],
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"Codice", type text}, {"Punteggio", type number}}),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Modificato tipo", "Lista Criteri", each Tabella2[Criterio]),
    #"Tabella Lista Criteri espansa" = Table.ExpandListColumn(#"Aggiunta colonna personalizzata", "Lista Criteri"),
    #"Aggiunta colonna personalizzata1" = Table.AddColumn(#"Tabella Lista Criteri espansa", "b-a", each if[Punteggio]-[Lista Criteri]>=0 then[Punteggio]-[Lista Criteri] else null),
    #"Aggiunta colonna personalizzata2" = Table.AddColumn(#"Aggiunta colonna personalizzata1", "a-b", each if [Lista Criteri]-[Punteggio]>=0 then [Lista Criteri]-[Punteggio] else null),
    #"Raggruppate righe" = Table.Group(#"Aggiunta colonna personalizzata2", {"Codice", "Punteggio"}, {{"b-a", each List.Min([#"b-a"]), type nullable number}, {"a-b", each List.Min([#"a-b"]), type nullable number}}),
    #"Aggiunta colonna personalizzata3" = Table.AddColumn(#"Raggruppate righe", "Valore Successivo matrice", each [Punteggio]+[#"a-b"]),
    #"Aggiunta colonna personalizzata4" = Table.AddColumn(#"Aggiunta colonna personalizzata3", "Valore Precedente Matrice", each [Punteggio]-[#"b-a"]),
    #"Aggiunta colonna personalizzata5" = Table.AddColumn(#"Aggiunta colonna personalizzata4", "Punteggio arrotondato", each if [Punteggio] = 0 then 0 else if [#"b-a"] > [#"a-b"] then [Valore Successivo matrice] else [Valore Precedente Matrice]),
    #"Riordinate colonne" = Table.ReorderColumns(#"Aggiunta colonna personalizzata5",{"Codice", "Punteggio", "Punteggio arrotondato", "b-a", "a-b", "Valore Successivo matrice", "Valore Precedente Matrice"})
in
    #"Riordinate colonne"
Allegati
Test.xlsx
(21.11 KiB) Scaricato 14 volte

Autore del topic
Astaga
Messaggi: 4 | Topic creati
Iscritto il: ven 8 ott 2021, 13:19

Powerquery - arrotondare secondo un criterio definito

Messaggio da Astaga »

Grazie Gian Jo anche questa è una ottima soluzione!
Rispondi