Creando Calender directamente en la consulta y agregando weeknr – hecho por Avi Singh

Desktop
Un usuario Pregunto ✅

bilingüe

Hola, Avi Singh ha creado este útil Calendario directamente en la consulta, pero también necesito los números de semana. ¿Alguien con experiencia M puede agregar números de semana a la consulta ?:

dejar
/ *
**** Este calendario fue creado y proporcionado por Avi Singh ****
**** Esto se puede compartir libremente siempre que se mantenga este comentario de texto. ****
http://www.youtube.com/PowerBIPro
www.LearnPowerBI.com por Avi Singh
* /
# «LearnPowerBI.com de Avi Singh» = 1,
StartDate = #fecha (2010, 1, 1),
EndDate = Date.EndOfYear (DateTime.Date (DateTime.FixedLocalNow ())) / * fue «#date (2017, 1, 1)» Actualizado en 201802027: el fin de año codificado de forma rígida provocó que algunas fórmulas se rompieran, cambiando a fecha dinámica * /,
// Usado para los cálculos de la columna ‘Offset’, puede codificar de forma rígida CurrentDate para la prueba, por ejemplo, #date (2017,9,1)
CurrentDate = DateTime.Date (DateTime.FixedLocalNow ()),
// Especifique el último mes de su año fiscal, por ejemplo, si junio es el último mes de su año fiscal, especifique 6
FiscalYearEndMonth = 6,
# «== ESTABLECER PARÁMETROS ARRIBA ==» = 1,
# «== Columna de fecha de compilación ==» = # «== ESTABLECER PARÁMETROS ARRIBA ==»,
ListDates = List.Dates (StartDate, Number.From (EndDate – StartDate) +1, #duration (1,0,0,0)),
# «Converted to Table» = Table.FromList (ListDates, Splitter.SplitByNothing (), null, null, ExtraValues.Error),
# «Columnas renombradas como fecha» = Table.RenameColumns (# «Converted to Table», {{«Column1», «Date»}}),
// En lo que respecta a Power BI, la columna ‘Fecha’ es todo lo que se necesita 🙂 Pero continuaremos y agregaremos algunas columnas amigables para los humanos
# «Tipo cambiado a fecha» = Table.TransformColumnTypes (# «Columnas renombradas como fecha», {{«Fecha», escriba fecha}}),
# «== Agregar columnas de calendario ==» = # «Tipo cambiado a la fecha»,
# «Agregado Calendar MonthNum» = Table.AddColumn (# «== Agregar Calendar Columns ==», «MonthNum», cada Date.Month ([Date]), Int64.Type),
# «Nombre del mes agregado» = Table.AddColumn (# «Calendario agregado MonthNum», «Month», cada Text.Start (Date.MonthName ([Date]), 3), escriba texto),
# «Nombre del mes agregado largo» = Table.AddColumn (# «Nombre del mes agregado», «MonthLong», cada Date.MonthName ([Date]), teclee el texto),
# «Trimestre de calendario agregado» = Table.AddColumn (# «Nombre de mes agregado largo», «Trimestre», cada «Q» y Text.From (Date.QuarterOfYear ([Date]))),
# «Año calendario agregado» = Table.AddColumn (# «Trimestre calendario agregado», «Año», cada Fecha.Año ​​([Date]), Int64.Type),
# «== Agregar columnas de calendario fiscal ==» = # «Año calendario agregado»,
# «Agregado FiscalMonthNum» = Table.AddColumn (# «== Agregar columnas de calendario fiscal ==», «FiscalMonthNum», cada uno si [MonthNum] > Año FiscalEndMonth
luego [MonthNum] – FiscalYearEndMonth
demás [MonthNum] + (12 – FiscalYearEndMonth), número de tipo),
# «Agregado FiscalMonth Name» = Table.AddColumn (# «Agregado FiscalMonthNum», «FiscalMonth», cada [Month]),
# «Agregado FiscalMonth Name Long» = Table.AddColumn (# «Agregado FiscalMonth Name», «FiscalMonthLong», cada [MonthLong]),
# «Agregado FiscalQuarter» = Table.AddColumn (# «Agregado FiscalMonth Name Long», «FiscalQuarter», cada «FQ» y Text.From (Number.RoundUp ([FiscalMonthNum] / 3,0))),
# «Año fiscal agregado» = Table.AddColumn (# «trimestre fiscal agregado», «año fiscal», cada «año fiscal» y
Text.End (
Texto. De (
si [MonthNum] > Año FiscalEndMonth
luego [Year] + 1
demás [Year]
)
, 2
)),

# «== Agregar columnas de compensación de fecha del calendario ==» = # «Año fiscal agregado»,
// Se puede usar, por ejemplo, para mostrar los últimos 3 meses (CurMonthOffset = 0, -1, -2)
# «Agregado CurMonthOffset» = Table.AddColumn (# «== Agregar columnas de compensación de fecha del calendario ==», «CurMonthOffset», cada (Date.Year ([Date]) – Fecha.Año ​​(Fecha actual)) * 12
+ Fecha.Mes ([Date]) – Date.Month (CurrentDate), Int64.Type),
// Se puede utilizar, por ejemplo, para mostrar los últimos 3 trimestres (CurQuarterOffset = 0, -1, -2)
# «Agregado CurQuarterOffset» = Table.AddColumn (# «Agregado CurMonthOffset», «CurQuarterOffset», cada / * Diferencia de año * /
(Fecha.Año ​​([Date]) – Fecha.Año ​​(Fecha actual)) * 4
/ * Diferencia de un cuarto * /
+ Number.RoundUp (Fecha.Mes ([Date]) / 3)
– Number.RoundUp (Fecha.Mes (Fecha actual) / 3),
Int64.Type),
// Se puede utilizar, por ejemplo, para mostrar los últimos 3 años (CurYearOffset = 0, -1, -2)
# «Agregado CurYearOffset» = Table.AddColumn (# «Agregado CurQuarterOffset», «CurYearOffset», cada Date.Year ([Date]) – Date.Year (CurrentDate), Int64.Type),
// Se puede utilizar, por ejemplo, para filtrar todas las fechas futuras
# «Agregado FutureDate Flag» = Table.AddColumn (# «Agregado CurYearOffset», «FutureDate», cada uno si [Date] > CurrentDate, luego «Future» o «Past»),
// FiscalYearOffset es el único Offset que es diferente.
// FiscalQuarterOffset = es igual que CurQuarterOffset
// FiscalMonthOffset = es igual que CurMonthOffset
# «== Agregar FiscalYearOffset ==» = # «Agregado indicador FutureDate»,
# «Filtered Rows to CurrentDate» = Table.SelectRows (# «== Agregar FiscalYearOffset ==», cada ([Date] = CurrentDate)),
CurrentFiscalYear = # «Filas filtradas a CurrentDate» {0}[FiscalYear],
# «Continuar … Orig Table» = # «== Agregar FiscalYearOffset ==»,
# «Agregado CurFiscalYearOffset» = Table.AddColumn (# «Continuar … Orig Table», «CurFiscalYearOffset», cada Number.From (Text.Range ([FiscalYear], 2,2)) –
Number.From (Text.Range (CurrentFiscalYear, 2,2))
/ * Extrae la parte numérica, por ejemplo, FY18 = 18 * /),
# «== Agregar columnas generales ==» = # «Agregado CurFiscalYearOffset»,
// Se usa como ‘Ordenar por columna’ para las columnas MonthYear
# «Added MonthYearNum» = Table.AddColumn (# «== Agregar columnas generales ==», «MonthYearNum», cada [Year]* 100 + [MonthNum] / * p. ej., septiembre de 2016 se convertiría en 201609 * /, Int64.Type),
# «Added MonthYear» = Table.AddColumn (# «Added MonthYearNum», «MonthYear», cada [Month] & «-» & Text.End (Text.From ([Year]), 2)),
# «Added MonthYearLong» = Table.AddColumn (# «Añadido MonthYear», «MonthYearLong», cada [Month] & «-» & Text.From ([Year])),
# «Agregado WeekdayNum» = Table.AddColumn (# «Agregado MonthYearLong», «WeekdayNum», cada Date.DayOfWeek ([Date]), Int64.Type),
# «Nombre de día de la semana agregado» = Table.AddColumn (# «WeekdayNum agregado», «Día de la semana», cada Text.Start (Date.DayOfWeekName ([Date]), 3), escriba texto),
# «Añadido WeekdayWeekend» = Table.AddColumn (# «Nombre del día de la semana añadido», «WeekdayWeekend», cada uno si [WeekdayNum] = 0 o [WeekdayNum] = 6
luego «Fin de semana»
else «Día de la semana»),
# «== Mejorar la tabla definitiva» = # «Se agregó WeekdayWeekend»,
# «—- Agregar WeekSequenceNum —-» = # «== Mejorar la tabla definitiva»,
# «Filtered Rows Sundays Only (Start of Week)» = Table.SelectRows (# «—- Agregar WeekSequenceNum —-«, cada ([WeekdayNum] = 0)),
# «Added Index WeekSequenceNum» = Table.AddIndexColumn (# «Filas filtradas solo domingos (inicio de semana)», «WeekSequenceNum», 2, 1),
# «Tabla definitiva de consultas fusionadas a WeekSequenceNum» = Table.NestedJoin (# «== Mejorar tabla definitiva», {«Fecha»}, # «Índice agregado WeekSequenceNum», {«Fecha»}, «Índice agregado WeekNum», JoinKind. LeftOuter),
# «NumSemana de índice agregado expandido» = Table.ExpandTableColumn (# «Tabla final de consultas fusionadas a WeekSequenceNum», «Índice agregado WeekNum», {«WeekSequenceNum»}, {«WeekSequenceNum»}),
// de alguna manera termina sin clasificar después de Expandir columna, no debería importar para la tabla final, pero hace que sea más difícil depurar y verificar que todo esté correcto. Clasificándolo así.
# «Filas reordenadas por fecha» = Tabla.Sort (# «Número de semana de índice agregado expandido», {{«Fecha», Orden.Ascendente}}),
# «Filled Down WeekSequenceNum» = Table.FillDown (# «Filas reordenadas por fecha», {«WeekSequenceNum»}),
# «Valor reemplazado WeekSequenceNum null con 1» = Table.ReplaceValue (# «Filled Down WeekSequenceNum», null, 1, Replacer.ReplaceValue, {«WeekSequenceNum»}),
# «—- WeekSequenceNum Complete —-» = # «Valor reemplazado WeekSequenceNum null por 1»,
Current_WeekSequenceNum = # «—- WeekSequenceNum Complete —-» {[Date = CurrentDate]}?[WeekSequenceNum],
# «Agregado CurWeekOffset personalizado» = Table.AddColumn (# «—- WeekSequenceNum Complete —-«, «CurWeekOffset», cada [WeekSequenceNum] – Current_WeekSequenceNum, Int64.Type),
# «Columnas eliminadas» = Table.RemoveColumns (# «CurWeekOffset personalizado agregado», {«WeekdayWeekend», «MonthYearLong», «FutureDate», «CurFiscalYearOffset», «FiscalQuarter», «FiscalYear», «FiscalMonthNum», «FiscalMonth», «FiscalMonthLong», «WeekSequenceNum»})
en
# «Columnas eliminadas»

EricHulshof

Oye, necesitas Date.WeekOfYear, consulte el código a continuación. 

let
/*
****This Calendar was created and provided by Avi Singh****
****This can be freely shared as long as this text comment is retained.****
http://www.youtube.com/PowerBIPro
www.LearnPowerBI.com by Avi Singh
*/
#"LearnPowerBI.com by Avi Singh" = 1,
StartDate = #date(2010, 1, 1),
EndDate = Date.EndOfYear(DateTime.Date(DateTime.FixedLocalNow())) /*was "#date(2017, 1, 1)" Updated on 201802027: hard Coded End of Year caused some formulas to break, switching to dynamic date*/,
//Used for 'Offset' Column calculations, you may Hard code CurrentDate for testing e.g. #date(2017,9,1)
CurrentDate = DateTime.Date(DateTime.FixedLocalNow()),
// Specify the last month in your Fiscal Year, e.g. if June is the last month of your Fiscal Year, specify 6
FiscalYearEndMonth = 6,
#"==SET PARAMETERS ABOVE==" = 1,
#"==Build Date Column==" = #"==SET PARAMETERS ABOVE==",
ListDates = List.Dates(StartDate, Number.From(EndDate - StartDate)+1, #duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(ListDates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns as Date" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
// As far as Power BI is concerned, the 'Date' column is all that is needed ‌‌ But we will continue and add a few Human-Friendly Columns
#"Changed Type to Date" = Table.TransformColumnTypes(#"Renamed Columns as Date",{{"Date", type date}}),
#"==Add Calendar Columns==" = #"Changed Type to Date",
#"Added Calendar MonthNum" = Table.AddColumn(#"==Add Calendar Columns==", "MonthNum", each Date.Month([Date]), Int64.Type),
#"Added Month Name" = Table.AddColumn(#"Added Calendar MonthNum", "Month", each Text.Start(Date.MonthName([Date]),3), type text),
#"Added Month Name Long" = Table.AddColumn(#"Added Month Name", "MonthLong", each Date.MonthName([Date]), type text),
#"Added Calendar Quarter" = Table.AddColumn(#"Added Month Name Long", "Quarter", each "Q" & Text.From(Date.QuarterOfYear([Date]))),
#"Added Calendar Year" = Table.AddColumn(#"Added Calendar Quarter", "Year", each Date.Year([Date]), Int64.Type),
#"==Add Fiscal Calendar Columns==" = #"Added Calendar Year",
#"Added FiscalMonthNum" = Table.AddColumn(#"==Add Fiscal Calendar Columns==", "FiscalMonthNum", each if [MonthNum] > FiscalYearEndMonth
then [MonthNum] - FiscalYearEndMonth
else [MonthNum] + (12 - FiscalYearEndMonth), type number),
#"Added FiscalMonth Name" = Table.AddColumn(#"Added FiscalMonthNum", "FiscalMonth", each [Month]),
#"Added FiscalMonth Name Long" = Table.AddColumn(#"Added FiscalMonth Name", "FiscalMonthLong", each [MonthLong]),
#"Added FiscalQuarter" = Table.AddColumn(#"Added FiscalMonth Name Long", "FiscalQuarter", each "FQ" & Text.From(Number.RoundUp([FiscalMonthNum] / 3,0))),
#"Added FiscalYear" = Table.AddColumn(#"Added FiscalQuarter", "FiscalYear", each "FY" &
Text.End(
Text.From(
if [MonthNum] > FiscalYearEndMonth
then [Year] + 1
else [Year]
)
, 2
)),

#"==Add Calendar Date Offset Columns==" = #"Added FiscalYear",
// Can be used to for example to show the past 3 months(CurMonthOffset = 0, -1, -2)
#"Added CurMonthOffset" = Table.AddColumn(#"==Add Calendar Date Offset Columns==", "CurMonthOffset", each ( Date.Year([Date]) - Date.Year(CurrentDate) ) * 12
+ Date.Month([Date]) - Date.Month(CurrentDate), Int64.Type),
// Can be used to for example to show the past 3 quarters (CurQuarterOffset = 0, -1, -2)
#"Added CurQuarterOffset" = Table.AddColumn(#"Added CurMonthOffset", "CurQuarterOffset", each /*Year Difference*/
( Date.Year([Date]) - Date.Year(CurrentDate) )*4
/*Quarter Difference*/
+ Number.RoundUp(Date.Month([Date]) / 3)
- Number.RoundUp(Date.Month(CurrentDate) / 3),
Int64.Type),
// Can be used to for example to show the past 3 years (CurYearOffset = 0, -1, -2)
#"Added CurYearOffset" = Table.AddColumn(#"Added CurQuarterOffset", "CurYearOffset", each Date.Year([Date]) - Date.Year(CurrentDate), Int64.Type),
// Can be used to for example filter out all future dates
#"Added FutureDate Flag" = Table.AddColumn(#"Added CurYearOffset", "FutureDate", each if [Date] > CurrentDate then "Future" else "Past" ),
// FiscalYearOffset is the only Offset that is different.
// FiscalQuarterOffset = is same as CurQuarterOffset
// FiscalMonthOffset = is same as CurMonthOffset
#"==Add FiscalYearOffset==" = #"Added FutureDate Flag",
#"Filtered Rows to CurrentDate" = Table.SelectRows(#"==Add FiscalYearOffset==", each ([Date] = CurrentDate)),
CurrentFiscalYear = #"Filtered Rows to CurrentDate"{0}[FiscalYear],
#"Continue...Orig Table" = #"==Add FiscalYearOffset==",
#"Added CurFiscalYearOffset" = Table.AddColumn(#"Continue...Orig Table", "CurFiscalYearOffset", each Number.From(Text.Range([FiscalYear],2,2)) -
Number.From(Text.Range(CurrentFiscalYear,2,2))
/*Extract the numerical portion, e.g. FY18 = 18*/),
#"==Add General Columns==" = #"Added CurFiscalYearOffset",
// Used as 'Sort by Column' for MonthYear columns
#"Added MonthYearNum" = Table.AddColumn(#"==Add General Columns==", "MonthYearNum", each [Year]*100 + [MonthNum] /*e.g. Sep-2016 would become 201609*/, Int64.Type),
#"Added MonthYear" = Table.AddColumn(#"Added MonthYearNum", "MonthYear", each [Month] & "-" & Text.End(Text.From([Year]),2)),
#"Added MonthYearLong" = Table.AddColumn(#"Added MonthYear", "MonthYearLong", each [Month] & "-" & Text.From([Year])),
#"Added WeekdayNum" = Table.AddColumn(#"Added MonthYearLong", "WeekdayNum", each Date.DayOfWeek([Date]), Int64.Type),
#"Added Weekday Name" = Table.AddColumn(#"Added WeekdayNum", "Weekday", each Text.Start(Date.DayOfWeekName([Date]),3), type text),
#"Added WeekdayWeekend" = Table.AddColumn(#"Added Weekday Name", "WeekdayWeekend", each if [WeekdayNum] = 0 or [WeekdayNum] = 6
then "Weekend"
else "Weekday"),
#"==Improve Ultimate Table" = #"Added WeekdayWeekend",
#"----Add WeekSequenceNum----" = #"==Improve Ultimate Table",
#"Filtered Rows Sundays Only (Start of Week)" = Table.SelectRows(#"----Add WeekSequenceNum----", each ([WeekdayNum] = 0)),
#"Added Index WeekSequenceNum" = Table.AddIndexColumn(#"Filtered Rows Sundays Only (Start of Week)", "WeekSequenceNum", 2, 1),
#"Merged Queries Ultimate Table to WeekSequenceNum" = Table.NestedJoin(#"==Improve Ultimate Table",{"Date"},#"Added Index WeekSequenceNum",{"Date"},"Added Index WeekNum",JoinKind.LeftOuter),
#"Expanded Added Index WeekNum" = Table.ExpandTableColumn(#"Merged Queries Ultimate Table to WeekSequenceNum", "Added Index WeekNum", {"WeekSequenceNum"}, {"WeekSequenceNum"}),
// somehow it ends up being unsorted after Expand Column, should not matter for the end table, but makes it harder to debug and check everything is correct. Thus sorting it.
#"ReSorted Rows by Date" = Table.Sort(#"Expanded Added Index WeekNum",{{"Date", Order.Ascending}}),
#"Filled Down WeekSequenceNum" = Table.FillDown(#"ReSorted Rows by Date",{"WeekSequenceNum"}),
#"Replaced Value WeekSequenceNum null with 1" = Table.ReplaceValue(#"Filled Down WeekSequenceNum",null,1,Replacer.ReplaceValue,{"WeekSequenceNum"}),
#"----WeekSequenceNum Complete----" = #"Replaced Value WeekSequenceNum null with 1",
Current_WeekSequenceNum = #"----WeekSequenceNum Complete----"{[Date = CurrentDate]}?[WeekSequenceNum],
#"Added Custom CurWeekOffset" = Table.AddColumn(#"----WeekSequenceNum Complete----", "CurWeekOffset", each [WeekSequenceNum] - Current_WeekSequenceNum, Int64.Type),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom CurWeekOffset",{"WeekdayWeekend", "MonthYearLong", "FutureDate", "CurFiscalYearOffset", "FiscalQuarter", "FiscalYear", "FiscalMonthNum", "FiscalMonth", "FiscalMonthLong", "WeekSequenceNum"}),
    #"Inserted Week of Year" = Table.AddColumn(#"Removed Columns", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type)
in
    #"Inserted Week of Year"

EricHulshof

Oye, necesitas Date.WeekOfYear, consulta el código a continuación. 

let
/*
****This Calendar was created and provided by Avi Singh****
****This can be freely shared as long as this text comment is retained.****
http://www.youtube.com/PowerBIPro
www.LearnPowerBI.com by Avi Singh
*/
#"LearnPowerBI.com by Avi Singh" = 1,
StartDate = #date(2010, 1, 1),
EndDate = Date.EndOfYear(DateTime.Date(DateTime.FixedLocalNow())) /*was "#date(2017, 1, 1)" Updated on 201802027: hard Coded End of Year caused some formulas to break, switching to dynamic date*/,
//Used for 'Offset' Column calculations, you may Hard code CurrentDate for testing e.g. #date(2017,9,1)
CurrentDate = DateTime.Date(DateTime.FixedLocalNow()),
// Specify the last month in your Fiscal Year, e.g. if June is the last month of your Fiscal Year, specify 6
FiscalYearEndMonth = 6,
#"==SET PARAMETERS ABOVE==" = 1,
#"==Build Date Column==" = #"==SET PARAMETERS ABOVE==",
ListDates = List.Dates(StartDate, Number.From(EndDate - StartDate)+1, #duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(ListDates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns as Date" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
// As far as Power BI is concerned, the 'Date' column is all that is needed ‌‌ But we will continue and add a few Human-Friendly Columns
#"Changed Type to Date" = Table.TransformColumnTypes(#"Renamed Columns as Date",{{"Date", type date}}),
#"==Add Calendar Columns==" = #"Changed Type to Date",
#"Added Calendar MonthNum" = Table.AddColumn(#"==Add Calendar Columns==", "MonthNum", each Date.Month([Date]), Int64.Type),
#"Added Month Name" = Table.AddColumn(#"Added Calendar MonthNum", "Month", each Text.Start(Date.MonthName([Date]),3), type text),
#"Added Month Name Long" = Table.AddColumn(#"Added Month Name", "MonthLong", each Date.MonthName([Date]), type text),
#"Added Calendar Quarter" = Table.AddColumn(#"Added Month Name Long", "Quarter", each "Q" & Text.From(Date.QuarterOfYear([Date]))),
#"Added Calendar Year" = Table.AddColumn(#"Added Calendar Quarter", "Year", each Date.Year([Date]), Int64.Type),
#"==Add Fiscal Calendar Columns==" = #"Added Calendar Year",
#"Added FiscalMonthNum" = Table.AddColumn(#"==Add Fiscal Calendar Columns==", "FiscalMonthNum", each if [MonthNum] > FiscalYearEndMonth
then [MonthNum] - FiscalYearEndMonth
else [MonthNum] + (12 - FiscalYearEndMonth), type number),
#"Added FiscalMonth Name" = Table.AddColumn(#"Added FiscalMonthNum", "FiscalMonth", each [Month]),
#"Added FiscalMonth Name Long" = Table.AddColumn(#"Added FiscalMonth Name", "FiscalMonthLong", each [MonthLong]),
#"Added FiscalQuarter" = Table.AddColumn(#"Added FiscalMonth Name Long", "FiscalQuarter", each "FQ" & Text.From(Number.RoundUp([FiscalMonthNum] / 3,0))),
#"Added FiscalYear" = Table.AddColumn(#"Added FiscalQuarter", "FiscalYear", each "FY" &
Text.End(
Text.From(
if [MonthNum] > FiscalYearEndMonth
then [Year] + 1
else [Year]
)
, 2
)),

#"==Add Calendar Date Offset Columns==" = #"Added FiscalYear",
// Can be used to for example to show the past 3 months(CurMonthOffset = 0, -1, -2)
#"Added CurMonthOffset" = Table.AddColumn(#"==Add Calendar Date Offset Columns==", "CurMonthOffset", each ( Date.Year([Date]) - Date.Year(CurrentDate) ) * 12
+ Date.Month([Date]) - Date.Month(CurrentDate), Int64.Type),
// Can be used to for example to show the past 3 quarters (CurQuarterOffset = 0, -1, -2)
#"Added CurQuarterOffset" = Table.AddColumn(#"Added CurMonthOffset", "CurQuarterOffset", each /*Year Difference*/
( Date.Year([Date]) - Date.Year(CurrentDate) )*4
/*Quarter Difference*/
+ Number.RoundUp(Date.Month([Date]) / 3)
- Number.RoundUp(Date.Month(CurrentDate) / 3),
Int64.Type),
// Can be used to for example to show the past 3 years (CurYearOffset = 0, -1, -2)
#"Added CurYearOffset" = Table.AddColumn(#"Added CurQuarterOffset", "CurYearOffset", each Date.Year([Date]) - Date.Year(CurrentDate), Int64.Type),
// Can be used to for example filter out all future dates
#"Added FutureDate Flag" = Table.AddColumn(#"Added CurYearOffset", "FutureDate", each if [Date] > CurrentDate then "Future" else "Past" ),
// FiscalYearOffset is the only Offset that is different.
// FiscalQuarterOffset = is same as CurQuarterOffset
// FiscalMonthOffset = is same as CurMonthOffset
#"==Add FiscalYearOffset==" = #"Added FutureDate Flag",
#"Filtered Rows to CurrentDate" = Table.SelectRows(#"==Add FiscalYearOffset==", each ([Date] = CurrentDate)),
CurrentFiscalYear = #"Filtered Rows to CurrentDate"{0}[FiscalYear],
#"Continue...Orig Table" = #"==Add FiscalYearOffset==",
#"Added CurFiscalYearOffset" = Table.AddColumn(#"Continue...Orig Table", "CurFiscalYearOffset", each Number.From(Text.Range([FiscalYear],2,2)) -
Number.From(Text.Range(CurrentFiscalYear,2,2))
/*Extract the numerical portion, e.g. FY18 = 18*/),
#"==Add General Columns==" = #"Added CurFiscalYearOffset",
// Used as 'Sort by Column' for MonthYear columns
#"Added MonthYearNum" = Table.AddColumn(#"==Add General Columns==", "MonthYearNum", each [Year]*100 + [MonthNum] /*e.g. Sep-2016 would become 201609*/, Int64.Type),
#"Added MonthYear" = Table.AddColumn(#"Added MonthYearNum", "MonthYear", each [Month] & "-" & Text.End(Text.From([Year]),2)),
#"Added MonthYearLong" = Table.AddColumn(#"Added MonthYear", "MonthYearLong", each [Month] & "-" & Text.From([Year])),
#"Added WeekdayNum" = Table.AddColumn(#"Added MonthYearLong", "WeekdayNum", each Date.DayOfWeek([Date]), Int64.Type),
#"Added Weekday Name" = Table.AddColumn(#"Added WeekdayNum", "Weekday", each Text.Start(Date.DayOfWeekName([Date]),3), type text),
#"Added WeekdayWeekend" = Table.AddColumn(#"Added Weekday Name", "WeekdayWeekend", each if [WeekdayNum] = 0 or [WeekdayNum] = 6
then "Weekend"
else "Weekday"),
#"==Improve Ultimate Table" = #"Added WeekdayWeekend",
#"----Add WeekSequenceNum----" = #"==Improve Ultimate Table",
#"Filtered Rows Sundays Only (Start of Week)" = Table.SelectRows(#"----Add WeekSequenceNum----", each ([WeekdayNum] = 0)),
#"Added Index WeekSequenceNum" = Table.AddIndexColumn(#"Filtered Rows Sundays Only (Start of Week)", "WeekSequenceNum", 2, 1),
#"Merged Queries Ultimate Table to WeekSequenceNum" = Table.NestedJoin(#"==Improve Ultimate Table",{"Date"},#"Added Index WeekSequenceNum",{"Date"},"Added Index WeekNum",JoinKind.LeftOuter),
#"Expanded Added Index WeekNum" = Table.ExpandTableColumn(#"Merged Queries Ultimate Table to WeekSequenceNum", "Added Index WeekNum", {"WeekSequenceNum"}, {"WeekSequenceNum"}),
// somehow it ends up being unsorted after Expand Column, should not matter for the end table, but makes it harder to debug and check everything is correct. Thus sorting it.
#"ReSorted Rows by Date" = Table.Sort(#"Expanded Added Index WeekNum",{{"Date", Order.Ascending}}),
#"Filled Down WeekSequenceNum" = Table.FillDown(#"ReSorted Rows by Date",{"WeekSequenceNum"}),
#"Replaced Value WeekSequenceNum null with 1" = Table.ReplaceValue(#"Filled Down WeekSequenceNum",null,1,Replacer.ReplaceValue,{"WeekSequenceNum"}),
#"----WeekSequenceNum Complete----" = #"Replaced Value WeekSequenceNum null with 1",
Current_WeekSequenceNum = #"----WeekSequenceNum Complete----"{[Date = CurrentDate]}?[WeekSequenceNum],
#"Added Custom CurWeekOffset" = Table.AddColumn(#"----WeekSequenceNum Complete----", "CurWeekOffset", each [WeekSequenceNum] - Current_WeekSequenceNum, Int64.Type),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom CurWeekOffset",{"WeekdayWeekend", "MonthYearLong", "FutureDate", "CurFiscalYearOffset", "FiscalQuarter", "FiscalYear", "FiscalMonthNum", "FiscalMonth", "FiscalMonthLong", "WeekSequenceNum"}),
    #"Inserted Week of Year" = Table.AddColumn(#"Removed Columns", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type)
in
    #"Inserted Week of Year"

Amitchandak

@bilingual, prueba

Date.DayOfWeek

https://docs.microsoft.com/en-us/powerquery-m/date-dayofweek

Aquí hay algunas columnas de la semana

https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns

Entradas relacionadas

Conversión de UTC a EST

Desktop

Un usuario Pregunto ✅ Daven Hola, Estoy buscando convertir la columna de fecha y hora UTC al horario de verano EST. ¿Cómo lo convierto en…

Informes de estilo Kanban

Desktop

Un usuario Pregunto ✅ Sachinhitachi Hola a todos, Tengo un requisito en el que el cliente necesita informes de estilo de panel Kanban. Intenté lograrlo…

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *