Spacca riga per Mese
Moderatore: Utilizzo_prof_Excel
-
- Messaggi: 16 | Topic creati
- Iscritto il: mar 14 set 2021, 13:52
- Ringraziato: 3 volte
Spacca riga per Mese
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:
Vorrei passare a 2 righe o più a seconda di quanti mesi dura la prenotazione (3 mesi --> 3 righe, 4 mesi --> 4 righe)
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
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:
Vorrei passare a 2 righe o più a seconda di quanti mesi dura la prenotazione (3 mesi --> 3 righe, 4 mesi --> 4 righe)
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 12 volte
-
- Messaggi: 2266 | Topic creati
- Iscritto il: dom 28 giu 2020, 19:41
- Luogo: Bologna
- Ringraziato: 673 volte
- Contatta:
Spacca riga per Mese
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
Andrea
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
-
Autore del topic - Messaggi: 16 | Topic creati
- Iscritto il: mar 14 set 2021, 13:52
- Ringraziato: 3 volte
-
Autore del topic - Messaggi: 16 | Topic creati
- Iscritto il: mar 14 set 2021, 13:52
- Ringraziato: 3 volte
Spacca riga per Mese
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!
dove fnSpaccaPeriodo è
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"
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
-
- Messaggi: 2266 | Topic creati
- Iscritto il: dom 28 giu 2020, 19:41
- Luogo: Bologna
- Ringraziato: 673 volte
- Contatta:
Spacca riga per Mese
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
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
-
Autore del topic - Messaggi: 16 | Topic creati
- Iscritto il: mar 14 set 2021, 13:52
- Ringraziato: 3 volte
Spacca riga per Mese
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!!!!
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!!!!
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"
-
- Messaggi: 2266 | Topic creati
- Iscritto il: dom 28 giu 2020, 19:41
- Luogo: Bologna
- Ringraziato: 673 volte
- Contatta:
Spacca riga per Mese
Ciao Gian Jo,
Avevo preparato anche un tutorial a riguardo, su una possibile logica da adottare:
viewtopic.php?f=23&t=575
Andrea
Avevo preparato anche un tutorial a riguardo, su una possibile logica da adottare:
viewtopic.php?f=23&t=575
Andrea
-
Autore del topic - Messaggi: 16 | Topic creati
- Iscritto il: mar 14 set 2021, 13:52
- Ringraziato: 3 volte