-
Notifications
You must be signed in to change notification settings - Fork 20
/
tabela-periodos-v1.0.1
86 lines (72 loc) · 7.71 KB
/
tabela-periodos-v1.0.1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
let
//Gera uma tabela
periodos = #table (
//Declaração das colunas e tipos
type table [
Periodo = text,
PeriodoOrdinal = Int64.Type,
PeriodoGrupo = text,
PeriodoGrupoOrdinal = Int64.Type,
Data = list
],
//Armazena em buffer a dCalendario
let dCalendario = Table.Buffer ( dCalendario ) in
//Lista das listas dos valores de cada campo
{
//Todo período
{ "Todo o período", 1, "Todo o período", 1, dCalendario[Data] },
//Dia
{ "Hoje", 1, "Dia", 2, Table.SelectRows ( dCalendario, each [DataOffset] = 0 )[Data] },
{ "Ontem", 2, "Dia", 2, Table.SelectRows ( dCalendario, each [DataOffset] = -1 )[Data] },
{ "Anteontem", 3, "Dia", 2, Table.SelectRows ( dCalendario, each [DataOffset] = -2 )[Data] },
{ "Últimos 7 dias", 4, "Dia", 2, Table.SelectRows ( dCalendario, each [DataOffset] >= -6 and [DataOffset] <= 0 )[Data] },
{ "Últimos 15 dias", 5, "Dia", 2, Table.SelectRows ( dCalendario, each [DataOffset] >= -14 and [DataOffset] <= 0 )[Data] },
{ "Últimos 30 dias", 6, "Dia", 2, Table.SelectRows ( dCalendario, each [DataOffset] >= -29 and [DataOffset] <= 0 )[Data] },
{ "Últimos 60 dias", 7, "Dia", 2, Table.SelectRows ( dCalendario, each [DataOffset] >= -59 and [DataOffset] <= 0 )[Data] },
{ "Últimos 90 dias", 8, "Dia", 2, Table.SelectRows ( dCalendario, each [DataOffset] >= -89 and [DataOffset] <= 0 )[Data] },
{ "Últimos 180 dias", 9, "Dia", 2, Table.SelectRows ( dCalendario, each [DataOffset] >= -179 and [DataOffset] <= 0 )[Data] },
{ "Últimos 365 dias", 10, "Dia", 2, Table.SelectRows ( dCalendario, each [DataOffset] >= -364 and [DataOffset] <= 0 )[Data] },
//Semana
{ "Esta semana", 11, "Semana", 3, Table.SelectRows ( dCalendario, each [SemanaOffset] = 0 )[Data] },
{ "Semana passada", 12, "Semana", 3, Table.SelectRows ( dCalendario, each [SemanaOffset] = -1 )[Data] },
{ "Semana retrasada", 13, "Semana", 3, Table.SelectRows ( dCalendario, each [SemanaOffset] = -2 )[Data] },
{ "Últimas 3 semanas", 14, "Semana", 3, Table.SelectRows ( dCalendario, each [SemanaOffset] >= -2 and [SemanaOffset] <= 0 )[Data] },
{ "Últimas 6 semanas", 15, "Semana", 3, Table.SelectRows ( dCalendario, each [SemanaOffset] >= -5 and [SemanaOffset] <= 0 )[Data] },
{ "Últimas 9 semanas", 16, "Semana", 3, Table.SelectRows ( dCalendario, each [SemanaOffset] >= -8 and [SemanaOffset] <= 0 )[Data] },
{ "Últimas 12 semanas", 17, "Semana", 3, Table.SelectRows ( dCalendario, each [SemanaOffset] >= -11 and [SemanaOffset] <= 0 )[Data] },
//Mês
{ "Este mês", 18, "Mês", 4, Table.SelectRows ( dCalendario, each [MesOffset] = 0 )[Data] },
{ "Mês passado", 19, "Mês", 4, Table.SelectRows ( dCalendario, each [MesOffset] = -1 )[Data] },
{ "Mês retrasado", 20, "Mês", 4, Table.SelectRows ( dCalendario, each [MesOffset] = -2 )[Data] },
{ "Últimos 3 meses", 21, "Mês", 4, Table.SelectRows ( dCalendario, each [MesOffset] >= -2 and [MesOffset] <= 0 )[Data] },
{ "Últimos 6 meses", 22, "Mês", 4, Table.SelectRows ( dCalendario, each [MesOffset] >= -5 and [MesOffset] <= 0 )[Data] },
{ "Últimos 9 meses", 23, "Mês", 4, Table.SelectRows ( dCalendario, each [MesOffset] >= -8 and [MesOffset] <= 0 )[Data] },
{ "Últimos 12 meses", 24, "Mês", 4, Table.SelectRows ( dCalendario, each [MesOffset] >= -11 and [MesOffset] <= 0 )[Data] },
//Trimestre
{ "Este trimestre", 25, "Trimestre", 5, Table.SelectRows ( dCalendario, each [TrimestreOffset] = 0 )[Data] },
{ "Trimestre passado", 26, "Trimestre", 5, Table.SelectRows ( dCalendario, each [TrimestreOffset] = -1 )[Data] },
{ "Trimestre retrasado", 27, "Trimestre", 5, Table.SelectRows ( dCalendario, each [TrimestreOffset] = -2 )[Data] },
{ "Últimos 3 trimestres", 28, "Trimestre", 5, Table.SelectRows ( dCalendario, each [TrimestreOffset] >= -2 and [TrimestreOffset] <= 0 )[Data] },
{ "Últimos 6 trimestres", 29, "Trimestre", 5, Table.SelectRows ( dCalendario, each [TrimestreOffset] >= -5 and [TrimestreOffset] <= 0 )[Data] },
{ "Últimos 9 trimestres", 30, "Trimestre", 5, Table.SelectRows ( dCalendario, each [TrimestreOffset] >= -8 and [TrimestreOffset] <= 0 )[Data] },
{ "Últimos 12 trimestres", 31, "Trimestre", 5, Table.SelectRows ( dCalendario, each [TrimestreOffset] >= -11 and [TrimestreOffset] <= 0 )[Data] },
//Ano
{ "Este ano", 32, "Ano", 6, Table.SelectRows ( dCalendario, each [AnoOffset] = 0 )[Data] },
{ "Ano passado", 33, "Ano", 6, Table.SelectRows ( dCalendario, each [AnoOffset] = -1 )[Data] },
{ "Ano retrasado", 34, "Ano", 6, Table.SelectRows ( dCalendario, each [AnoOffset] = -2 )[Data] },
{ "Últimos 2 anos", 35, "Ano", 6, Table.SelectRows ( dCalendario, each [AnoOffset] >= -2 and [AnoOffset] <= 0 )[Data] },
{ "Últimos 3 anos", 36, "Ano", 6, Table.SelectRows ( dCalendario, each [AnoOffset] >= -5 and [AnoOffset] <= 0 )[Data] },
{ "Últimos 4 anos", 37, "Ano", 6, Table.SelectRows ( dCalendario, each [AnoOffset] >= -8 and [AnoOffset] <= 0 )[Data] },
{ "Últimos 5 anos", 38, "Ano", 6, Table.SelectRows ( dCalendario, each [AnoOffset] >= -11 and [AnoOffset] <= 0 )[Data] },
//Até
{ "Até hoje", 39, "Até", 7, Table.SelectRows ( dCalendario, each [DataOffset] <= 0 )[Data] },
{ "Até ontem", 40, "Até", 7, Table.SelectRows ( dCalendario, each [DataOffset] <= -1 )[Data] },
{ "Até anteontem", 41, "Até", 7, Table.SelectRows ( dCalendario, each [DataOffset] <= -2 )[Data] }
}
),
//Expande a coluna de lista Data
datasPeriodoExpandido = Table.ExpandListColumn ( periodos, "Data" ),
//Altera o tipo da coluna Data para data
tipoAlterado = Table.TransformColumnTypes ( datasPeriodoExpandido, {{"Data", type date}} )
in
tipoAlterado