vanlang
Hola a todos,
Tengo un problema. Espero que alguien pueda ayudarme. Tengo una tabla similar a la siguiente:
Número de proyecto | Tipo de monitoreo | Inicio del Proyecto | Fin del Proyecto |
A | Anual | 1 de abril de 2020 | 31 de marzo de 2022 |
B | Trimestral | 1 de abril de 2020 | 31 de marzo de 2021 |
Me gustaría tener la tabla expandida dinámicamente para ser la siguiente:
Proyecto | Tipo de monitoreo | Inicio del proyecto | Fin del proyecto | Supervisar inicio | Supervisar fin |
A | Anual | 1 de abril de 2020 | 31 de marzo de 2022 | 1 de abril de 2020 | 31 de marzo de 2021 |
A | Anual | 1 de abril de 2020 | 31 de marzo de 2022 | 1 de abril de 2021 | 31 de marzo de 2022 |
B | Trimestral | 1 de abril de 2020 | 31 de marzo de 2021 | 1 de abril de 2020 | 30 de junio de 2020 |
B | Trimestral | 1 de abril de 2020 | 31 de marzo de 2021 | 1 de julio de 2020 | 30 de septiembre de 2020 |
B | Trimestral | 1 de abril de 2020 | 31 de marzo de 2021 | 1 de octubre de 2020 | 31 de diciembre de 2020 |
B | Trimestral | 1 de abril de 2020 | 31 de marzo de 2021 | 1 de enero de 2021 | 31 de marzo de 2021 |
Básicamente, lo que estoy tratando de lograr es que cada fila de la tabla que puse tenga filas agregadas para el tipo de período de monitoreo según el inicio y el final del proyecto.
No sé si es posible, pero cualquier ayuda sería apreciada.
Gracias por adelantado,
Vanlang
Jakintá
Hola,
Debería hacerse más ordenado, pero hasta que alguien lo publique, esto podría servir…
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start of Project", type date}, {"End of Project", type date}}),
Duration = Table.AddColumn(#"Changed Type", "Age",
each if [Monitoring Type] = "Yearly"
then List.Numbers(0,Number.Round(Duration.TotalDays([End of Project] - [Start of Project])/365,0),12)
else List.Numbers(0,Number.Round(Duration.TotalDays([End of Project] - [Start of Project])/91.25,0),3)),
Expanded = Table.ExpandListColumn(Duration, "Age"),
MonitorStart = Table.AddColumn(Expanded, "Monitor Start", each Date.AddMonths([Start of Project], [Age])),
MonitorEnd = Table.AddColumn(MonitorStart, "Monitor End", each if [Monitoring Type] = "Yearly"
then Date.EndOfMonth(Date.AddMonths([Start of Project], [Age]+11))
else Date.EndOfMonth(Date.AddMonths([Start of Project], [Age]+2))),
Final = Table.RemoveColumns(MonitorEnd,{"Age"})
in
Final
edhans
Vea si esto funciona para usted @vanlang: es un poco más complejo que lo que hizo @Jakinta, pero estoy usando las funciones de fecha aquí para agregar años o trimestres, por lo que manejaría los años bisiestos automáticamente.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUYpMTSzKqQQyHAuKMnMUDHUUjAyMDIB838Si5AwFY4iAkVKsTrSSE1A4sDSxqCSVCC2GSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project Number" = _t, #"Monitoring Type" = _t, #"Start of Project" = _t, #"End of Project" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start of Project", type date}, {"End of Project", type date}}),
Year = Table.SelectRows(#"Changed Type", each [Monitoring Type] = "Yearly"),
YearDateRange =
List.Generate(
() => [x = Year[Start of Project]{0}],
each [x] < Year[End of Project]{0},
each [x = Date.AddYears([x], 1)],
each [x]
),
Quarter = Table.SelectRows(#"Changed Type", each [Monitoring Type] = "Quarterly"),
QuarterDateRange =
List.Generate(
() => [x = Quarter[Start of Project]{0}],
each [x] < Quarter[End of Project]{0},
each [x = Date.AddQuarters([x], 1)],
each [x]
),
NewDates =
Table.AddColumn(#"Changed Type", "Monitor Start", each if [Monitoring Type] = "Yearly" then YearDateRange
else QuarterDateRange),
#"Expanded Range" = Table.ExpandListColumn(NewDates, "Monitor Start"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Range",{{"Monitor Start", type date}}),
#"Added Monitor End" =
Table.AddColumn(
#"Changed Type1",
"Monitor End",
each if [Monitoring Type] = "Yearly" then Date.AddDays(Date.AddYears([Monitor Start], 1), -1)
else Date.AddDays(Date.AddQuarters([Monitor Start], 1), -1),
Date.Type
)
in
#"Added Monitor End"
Se generan dos listas, una YearDateRange y QuarterDateRange. La lista de trimestres se ve así:
Comienza con la primera fecha, luego sigue agregando trimestres hasta que no sea inferior a la fecha de finalización.
La lista de años funciona de la misma manera, pero agrega años.
Luego agregué una columna final de Montitor End que es simplemente un año/trimestre más menos un día.
Resultado final:
Cómo usar el código M proporcionado en una consulta en blanco:
1) En Power Query, seleccione Nueva fuente, luego Consulta en blanco
2) En la cinta Inicio, seleccione el botón «Editor avanzado»
3) Elimina todo lo que ves, luego pega el código M que te he dado en ese cuadro.
4) Presiona Listo
5) Consulte este artículo si necesita ayuda para usar este código M en su modelo.
vanlang
En respuesta a edhans
Esto es tan brillante @edhans ¡Muchas gracias!
edhans
En respuesta a vanlang
Me alegro de haber podido ayudar a @vanlang
Jakintá
Hola,
Debería hacerse más ordenado, pero hasta que alguien lo publique, esto podría servir…
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start of Project", type date}, {"End of Project", type date}}),
Duration = Table.AddColumn(#"Changed Type", "Age",
each if [Monitoring Type] = "Yearly"
then List.Numbers(0,Number.Round(Duration.TotalDays([End of Project] - [Start of Project])/365,0),12)
else List.Numbers(0,Number.Round(Duration.TotalDays([End of Project] - [Start of Project])/91.25,0),3)),
Expanded = Table.ExpandListColumn(Duration, "Age"),
MonitorStart = Table.AddColumn(Expanded, "Monitor Start", each Date.AddMonths([Start of Project], [Age])),
MonitorEnd = Table.AddColumn(MonitorStart, "Monitor End", each if [Monitoring Type] = "Yearly"
then Date.EndOfMonth(Date.AddMonths([Start of Project], [Age]+11))
else Date.EndOfMonth(Date.AddMonths([Start of Project], [Age]+2))),
Final = Table.RemoveColumns(MonitorEnd,{"Age"})
in
Final
vanlang
En respuesta a Jakintá
@Jakinta
¡Esto es tan brillante! Todavía no entiendo muy bien la lógica, ¡pero es brillante! ¡Gracias Gracias! ¡Llevo días trabajando en ello!