Spacca riga per Mese

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

Moderatore: Utilizzo_prof_Excel

Rispondi
Avatar utente

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

Spacca riga per Mese

Messaggio da Gian Jo »

Ciao ragazzi,


Vi sottopongo un problema che dopo vari sbattimenti di testa avrei risolto ma la soluzione non mi convince tantissimo. Secondo me ci sono metodi migliori per affrontarlo e risolverlo.

In un file Excel ci sono dei dati relativi a dei check-in / check-out per degli appartamenti. Questi dati sono inseriti tramite una form.
Il problema che dovevo risolvere è quello di spaccare la riga PER MESE se la data di check in e la data di check out si trovano in un mese che non è lo stesso. Spaccando la riga poi riesco a spalmare l'importo incassato dalla vendita della prenotazione in base ai giorni effettivi di ciascun mese.
Per fare questo giochino in pratica devo creare delle nuove righe se i mesi non corrispondono.

In pratica da una riga singola che si presenta cosi:

Immagine

Vorrei passare a 2 righe o più a seconda di quanti mesi dura la prenotazione (3 mesi --> 3 righe, 4 mesi --> 4 righe)

Immagine

Pe risolvere la cosa avevo diverse possibilità:
1) la prima tramite VBA all'inserimento del record perchè i dati vengono inseriti tramite una Form ma poi sporcavo la base dati iniziale e quindi no
2) Power Query dove carico i dati.. ho fatto una formula abbastanza intricata con List.Accumulate e più o meno funziona ma la soluzione non mi piace e vi allego il foglio.

Vi chiederei...voi come avreste risolto il problema..ci sono modi più inteligenti e meno incasinati...
Tutto questo a me serve per fare un report relativo agli incassi spaccati per Mese....
Grazie in anticipo
Allegati
Domanda_MondoBI.xlsb
(91.45 KiB) Scaricato 11 volte


Avatar utente

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

Spacca riga per Mese

Messaggio da Andrea90 »

Ciao Gian Jo,

Io userei una logica simile a quella che ho utilizzato per la soluzione di questa discussione (invece del numero di rate prendi il numero dei mesi).

Link: viewtopic.php?f=9&t=559

Qualcosa di simile

Codice: Seleziona tutto

 let
    Origine = Excel.CurrentWorkbook(){[Name="Tabella3"]}[Content],
    #"Rimosse colonne" = Table.RemoveColumns(Origine,{"Codice prenotazione", "Nazionalità", "N. di adulti", "N. di bambini", "Spese di Pulizia (€)", "Commissioni (€)", "Tassa di soggiorno (€)", "Data di prenotazione", "N. di notti"}),
    MonthList = Table.AddColumn(#"Rimosse colonne", "Temp List", each {Date.Month([Data di inizio])..Date.Month([Data di fine])}),
    #"Tabella Temp List espansa" = Table.ExpandListColumn(MonthList, "Temp List"),
    New_StartDate = Table.AddColumn(#"Tabella Temp List espansa", "New_Data__Start", each if Date.Month([Data di inizio]) = [Temp List] then [Data di inizio] else #date(Date.Year([Data di inizio]), [Temp List], 1)),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(New_StartDate, "New_EndDate", each if Date.Month([Data di fine]) = [Temp List] then [Data di fine] else Date.EndOfMonth(#date(Date.Year([Data di fine]), [Temp List], 1))),
    #"Modificato tipo1" = Table.TransformColumnTypes(#"Aggiunta colonna personalizzata",{{"Portale", type text}, {"Appartamento", type text}, {"Nome ospite", type text}, {"Data di inizio", type date}, {"Data di fine", type date}, {"Importo Netto (€)", type number}, {"Temp List", Int64.Type}, {"New_Data__Start", type date}, {"New_EndDate", type date}}),
    TotGiorni = Table.AddColumn(#"Modificato tipo1", "Tot Giorni", each Duration.Days([New_EndDate] - [New_Data__Start])+1, Int64.Type)
in
    TotGiorni
Andrea
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
Avatar utente

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

Spacca riga per Mese

Messaggio da Gian Jo »

Grazie mille!
DOmani ci provo e ti do un riscontro!
Avatar utente

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

Spacca riga per Mese

Messaggio da Gian Jo »

Ciao! Ho visto la soluzione e potrebbe essere una valida alterntiva anche se ho dei problemi sul calcolo dei giorni precisi su ogni mese per rispalmare l'importo. Alla fine ho risolto cosi !
Grazie per la risposta!

Codice: Seleziona tutto


let
    Origine = Excel.CurrentWorkbook(){[Name="Dati"]}[Content],
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"Portale", type text}, {"Appartamento", type text}, {"Nome ospite", type text}, {"Codice prenotazione", type text}, {"Nazionalità", type text}, {"N. di adulti", Int64.Type}, {"N. di bambini", Int64.Type}, {"Mese", type text}, {"Data di inizio", type date}, {"Data di fine", type date}, {"N. di notti", Int64.Type}, {"Importo Netto (€)", type number}, {"Spese di Pulizia (€)", Int64.Type}, {"Commissioni (€)", type any}, {"Tassa di soggiorno (€)", type text}, {"Data di prenotazione", type date}}),
    #"Funzione personalizzata richiamata" = Table.AddColumn(#"Modificato tipo", "Custom", each fnSpaccaPeriodo([Data di inizio], [Data di fine])),
    #"Tabella Custom espansa" = Table.ExpandTableColumn(#"Funzione personalizzata richiamata", "Custom", {"fDate", "fDay"}, {"fDate", "fDay"}),
    #"Funzione SpaccaPeriodo" = Table.AddColumn(#"Tabella Custom espansa", "Nuovo Importo", each Number.Round(if [fDay]=0 then 
[#"Importo Netto (€)"] 
else 
[#"Importo Netto (€)"]/[N. di notti]*[fDay],2)),
    #"Estratto Nome del Mese" = Table.AddColumn(#"Funzione SpaccaPeriodo", "Nome Mese", each Date.MonthName([fDate])),
    #"Nuovo Colonna Numero Notti" = Table.AddColumn(#"Estratto Nome del Mese", "Numero Notti", each if [fDay] = 0 then [N. di notti] else [fDay]),
    #"Rimosse colonne" = Table.RemoveColumns(#"Nuovo Colonna Numero Notti",{"N. di notti", "Importo Netto (€)", "Spese di Pulizia (€)", "Commissioni (€)", "Tassa di soggiorno (€)", "Data di prenotazione", "fDate", "fDay"})
in
    #"Rimosse colonne"
dove fnSpaccaPeriodo è

Codice: Seleziona tutto

let
    Origine = (pStart as date, pEnd as date) => let
        Origine =  if (Date.Month(pStart) <> Date.Month(pEnd)) and not ((Date.Day(pEnd) = 1) and (Date.Month(pEnd) = Date.Month(Date.AddMonths(pStart,1))))  then
                    List.Generate(
                    () => [fDate = pStart, fDay = Date.DaysInMonth(fDate) - Date.Day(fDate) +1],
                    each [fDate] <= pEnd,
                    each
                        let EoM = Date.EndOfMonth( Date.AddMonths([fDate],1)) in
                    if EoM > pEnd then
                        [fDate = Date.AddDays(Date.EndOfMonth([fDate]), Date.Day(pEnd)), fDay = Date.Day(fDate) -1 ]
                    else
                        [fDate = EoM , fDay = Date.Day(fDate)]
                    )
                else
                    {[fDate = pStart, fDay = 0] },
        #"Conversione in tabella" = Table.FromList(Origine, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Tabella Column1 espansa" = Table.ExpandRecordColumn(#"Conversione in tabella", "Column1", {"fDate", "fDay"}, {"fDate", "fDay"})
    in
        #"Tabella Column1 espansa"
in
    Origine
Avatar utente

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

Spacca riga per Mese

Messaggio da Andrea90 »

Ciao Gian Jo,

Il calcolo dei giorni alla fine si traduce in una serie di condizioni if..then..else da impostare sulla base delle tue esigenze.

Secondo me hai complicato i calcoli utilizzando quel List.Generate() che assieme a List.Accumulate() sono due funzioni che mi guarderei bene dall utilizzare perché in termini di performance sono davvero scarse ed il più delle volte si ottengono risultati equivalenti tramite altre vie.

Andrea
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
Avatar utente

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

Spacca riga per Mese

Messaggio da Gian Jo »

Ciao,
Posto la soluzione con il tuo approccio utilizzato per risolvere il problema delle rate di cui sopra.
Ho provato per evitare List.Generate(). Più o meno ce l'ho fatta solo che mi devo espandere per colonne per quanti mesi trovo in quanto le condizioni da verificare avrebbero bisogno di un ciclo.
Grazie dei consigli!!!! :wave: :thumbup:

Codice: Seleziona tutto

let
    Origine = Excel.CurrentWorkbook(){[Name="Dati"]}[Content],
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"Portale", type text}, {"Appartamento", type text}, {"Nome ospite", type text}, {"Codice prenotazione", type text}, {"Nazionalità", type text}, {"N. di adulti", Int64.Type}, {"N. di bambini", Int64.Type}, {"Mese", type text}, {"Data di inizio", type date}, {"Data di fine", type date}, {"N. di notti", Int64.Type}, {"Importo Netto (€)", type number}, {"Spese di Pulizia (€)", Int64.Type}, {"Commissioni (€)", type number}, {"Tassa di soggiorno (€)", type text}, {"Data di prenotazione", type datetime}}),
    #"Numero Righe/Mesi da Aggiungere" = Table.AddColumn(#"Modificato tipo", "NumeroRighe", each if (Date.Month([Data di fine]) - Date.Month([Data di inizio]) > 0) and not ((Date.Day([Data di fine]) = 1) and (Date.Month([Data di fine]) = Date.Month(Date.AddMonths([Data di inizio],1)))) 
then
(Date.Month([Data di fine]) - Date.Month([Data di inizio])+1)
else
0),
    #"Creazione Lista Mesi" = Table.AddColumn(#"Numero Righe/Mesi da Aggiungere", "ListaMesiDaAggiungere", each {1..[NumeroRighe]}),
    #"Tabella ListaMesiDaAggiungere espansa" = Table.ExpandListColumn(#"Creazione Lista Mesi", "ListaMesiDaAggiungere"),
    NumeroGiorniMese1 = Table.AddColumn(#"Tabella ListaMesiDaAggiungere espansa", "GiorniMese1", each if [ListaMesiDaAggiungere] = 1 then
Date.AddMonths(Date.StartOfMonth([Data di inizio]),[ListaMesiDaAggiungere]) - [Data di inizio]
else
null),
    NumeroGiorniMese2 = Table.AddColumn(NumeroGiorniMese1, "GiorniMese2", each if [ListaMesiDaAggiungere] = 2 then 
if [Data di fine]> Date.AddMonths(Date.StartOfMonth([Data di inizio]),[ListaMesiDaAggiungere]-1) and [Data di fine]<= Date.AddMonths(Date.EndOfMonth([Data di inizio]),[ListaMesiDaAggiungere]-1)
then
[Data di fine] - Date.AddMonths(Date.StartOfMonth([Data di inizio]),[ListaMesiDaAggiungere]-1)
else 
Date.AddMonths(Date.EndOfMonth([Data di inizio]),[ListaMesiDaAggiungere]-1) - Date.AddMonths(Date.StartOfMonth([Data di inizio]),[ListaMesiDaAggiungere]-1) 
else
null),
    NumeroGiorniMese3 = Table.AddColumn(NumeroGiorniMese2, "GiorniMese3", each if [ListaMesiDaAggiungere] = 3 then 
if [Data di fine]> Date.AddMonths(Date.StartOfMonth([Data di inizio]),[ListaMesiDaAggiungere]-1) and [Data di fine]<= Date.AddMonths(Date.EndOfMonth([Data di inizio]),[ListaMesiDaAggiungere]-1)then
[Data di fine] - Date.AddMonths(Date.StartOfMonth([Data di inizio]),[ListaMesiDaAggiungere]-1)
else 
Date.AddMonths(Date.EndOfMonth([Data di inizio]),[ListaMesiDaAggiungere]-1) - Date.AddMonths(Date.StartOfMonth([Data di inizio]),[ListaMesiDaAggiungere]-1) 
else
null),
    NumeroGiorniMese4 = Table.AddColumn(NumeroGiorniMese3, "GiorniMese4", each if [ListaMesiDaAggiungere] = 4 then 
if [Data di fine]> Date.AddMonths(Date.StartOfMonth([Data di inizio]),[ListaMesiDaAggiungere]-1) and [Data di fine]<= Date.AddMonths(Date.EndOfMonth([Data di inizio]),[ListaMesiDaAggiungere]-1)then
[Data di fine] - Date.AddMonths(Date.StartOfMonth([Data di inizio]),[ListaMesiDaAggiungere]-1)
else 
Date.AddMonths(Date.EndOfMonth([Data di inizio]),[ListaMesiDaAggiungere]-1) - Date.AddMonths(Date.StartOfMonth([Data di inizio]),[ListaMesiDaAggiungere]-1) 
else
null),
    #"Modificato tipo1" = Table.TransformColumnTypes(NumeroGiorniMese4,{{"GiorniMese1", Int64.Type}, {"GiorniMese2", Int64.Type}, {"GiorniMese3", Int64.Type}, {"GiorniMese4", Int64.Type}}),
    #"Merge di colonne" = Table.CombineColumns(Table.TransformColumnTypes(#"Modificato tipo1", {{"GiorniMese1", type text}, {"GiorniMese2", type text}, {"GiorniMese3", type text}, {"GiorniMese4", type text}}, "it-IT"),{"GiorniMese1", "GiorniMese2", "GiorniMese3", "GiorniMese4"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Nuovo Numero Notti"),
    #"Modificato tipo2" = Table.TransformColumnTypes(#"Merge di colonne",{{"Nuovo Numero Notti", Int64.Type}}),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Modificato tipo2", "Nuovo Importo Spalmato", each [#"Importo Netto (€)"]/[N. di notti]*[Nuovo Numero Notti])
in
    #"Aggiunta colonna personalizzata"


Avatar utente

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

Spacca riga per Mese

Messaggio da Andrea90 »

Ciao Gian Jo,

Avevo preparato anche un tutorial a riguardo, su una possibile logica da adottare:

viewtopic.php?f=23&t=575

Andrea
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
Avatar utente

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

Spacca riga per Mese

Messaggio da Gian Jo »

Ciao!
Ho visto!Complimenti!
Direi la soluzione perfetta!
GianPaolo
Rispondi