Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I have this script, and I would like the period_namber and period_name columns to follow the same criteria as the week_namber column.
In this example, week 05 is returning periods 1 and 2, and the correct option for my case would be to return only week 01.
and on the first day of week 06 return P02
Consider Monday as the first day of the week. Is it possible to make corrections to meet these criteria?
Here is the script:
let
startDate = #date(2024, 1, 1),
endDate = #date(Date.Year(DateTime.LocalNow()), 12, 31),
numDays = Duration.From(endDate - startDate),
dateList = List.Dates(startDate, Number.From(numDays) + 1, #duration(1, 0, 0, 0)),
dateTable = Table.FromList(dateList, Splitter.SplitByNothing()),
renamedTable = Table.RenameColumns(dateTable, {{"Column1", "date_short"}}),
numDayColumn = Table.AddColumn(renamedTable, "num_day", each Date.Day([date_short]), Int64.Type),
numMonthColumn = Table.AddColumn(numDayColumn, "num_month", each Date.Month([date_short]), Int64.Type),
numYearColumn = Table.AddColumn(numMonthColumn, "num_year", each Date.Year([date_short]), Int64.Type),
nameMonthBrColumn = Table.AddColumn(numYearColumn, "name_month_br", each Date.ToText([date_short], "MMMM"), type text),
nameMonthUsColumn = Table.AddColumn(nameMonthBrColumn, "name_month_us", each Date.ToText([date_short], "MMMM", "en-US"), type text),
nameMonthShortBrColumn = Table.AddColumn(nameMonthUsColumn, "name_month_short_br", each Text.Start([name_month_br], 3), type text),
nameMonthShortUsColumn = Table.AddColumn(nameMonthShortBrColumn, "name_month_short_us", each Text.Start([name_month_us], 3), type text),
numWeekdayColumn = Table.AddColumn(nameMonthShortUsColumn, "num_weekday", each Date.DayOfWeek([date_short], Day.Monday), Int64.Type),
nameWeekdayShortBrColumn = Table.AddColumn(numWeekdayColumn, "name_weekday_short_br", each Text.Start(Date.ToText([date_short], "dddd"), 3), type text),
nameWeekdayBrColumn = Table.AddColumn(nameWeekdayShortBrColumn, "name_weekday_br", each Date.ToText([date_short], "dddd"), type text),
nameWeekdayShort2BrColumn = Table.AddColumn(nameWeekdayBrColumn, "name_weekday_short2_br", each Text.Start([name_weekday_br], 3), type text),
nameWeekdayUsColumn = Table.AddColumn(nameWeekdayShort2BrColumn, "name_weekday_us", each Date.ToText([date_short], "dddd", "en-US"), type text),
nameWeekdayShortUsColumn = Table.AddColumn(nameWeekdayUsColumn, "name_weekday_short_us", each Text.Start([name_weekday_us], 3), type text),
weekNumberColumn = Table.AddColumn(nameWeekdayShortUsColumn, "week_number", each Date.WeekOfYear([date_short], Day.Monday), Int64.Type),
weekNameColumn = Table.AddColumn(weekNumberColumn, "week_name", each "W" & Text.PadStart(Text.From([week_number]), 2, "0"), type text),
periodNumberColumn = Table.AddColumn(weekNameColumn, "period_number", each Date.Month([date_short]), Int64.Type),
periodNameColumn = Table.AddColumn(periodNumberColumn, "period_name", each if [period_number] < 10 then "P0" & Text.From([period_number]) else "P" & Text.From([period_number]), type text),
finalTable = Table.SelectColumns(periodNameColumn, {"date_short", "num_day", "num_month", "num_year", "name_month_br", "name_month_us", "name_month_short_br", "name_month_short_us","num_weekday", "name_weekday_short_br", "name_weekday_br", "name_weekday_short2_br", "name_weekday_us", "name_weekday_short_us", "week_number", "week_name", "period_number", "period_name"}),
#"Tipo Alterado" = Table.TransformColumnTypes(finalTable, {{"date_short", type date}})
in
#"Tipo Alterado"
Solved! Go to Solution.
Hi @jcnascime, I've updated few steps:
let
startDate = #date(2024, 1, 1),
endDate = #date(Date.Year(DateTime.LocalNow()), 12, 31),
numDays = Duration.From(endDate - startDate),
dateList = List.Dates(startDate, Number.From(numDays) + 1, #duration(1, 0, 0, 0)),
dateTable = Table.FromList(dateList, Splitter.SplitByNothing(), type table[date_short=date]),
numDayColumn = Table.AddColumn(dateTable, "num_day", each Date.Day([date_short]), Int64.Type),
numMonthColumn = Table.AddColumn(numDayColumn, "num_month", each Date.Month([date_short]), Int64.Type),
numYearColumn = Table.AddColumn(numMonthColumn, "num_year", each Date.Year([date_short]), Int64.Type),
nameMonthBrColumn = Table.AddColumn(numYearColumn, "name_month_br", each Date.ToText([date_short], "MMMM"), type text),
nameMonthUsColumn = Table.AddColumn(nameMonthBrColumn, "name_month_us", each Date.ToText([date_short], "MMMM", "en-US"), type text),
nameMonthShortBrColumn = Table.AddColumn(nameMonthUsColumn, "name_month_short_br", each Text.Start([name_month_br], 3), type text),
nameMonthShortUsColumn = Table.AddColumn(nameMonthShortBrColumn, "name_month_short_us", each Text.Start([name_month_us], 3), type text),
numWeekdayColumn = Table.AddColumn(nameMonthShortUsColumn, "num_weekday", each Date.DayOfWeek([date_short], Day.Monday), Int64.Type),
nameWeekdayShortBrColumn = Table.AddColumn(numWeekdayColumn, "name_weekday_short_br", each Text.Start(Date.ToText([date_short], "dddd"), 3), type text),
nameWeekdayBrColumn = Table.AddColumn(nameWeekdayShortBrColumn, "name_weekday_br", each Date.ToText([date_short], "dddd"), type text),
nameWeekdayShort2BrColumn = Table.AddColumn(nameWeekdayBrColumn, "name_weekday_short2_br", each Text.Start([name_weekday_br], 3), type text),
nameWeekdayUsColumn = Table.AddColumn(nameWeekdayShort2BrColumn, "name_weekday_us", each Date.ToText([date_short], "dddd", "en-US"), type text),
nameWeekdayShortUsColumn = Table.AddColumn(nameWeekdayUsColumn, "name_weekday_short_us", each Text.Start([name_weekday_us], 3), type text),
weekNumberColumn = Table.AddColumn(nameWeekdayShortUsColumn, "week_number", each Date.WeekOfYear([date_short], Day.Monday), Int64.Type),
weekNameColumn = Table.AddColumn(weekNumberColumn, "week_name", each "W" & Text.PadStart(Text.From([week_number]), 2, "0"), type text),
periodNumberColumn = Table.AddColumn(weekNameColumn, "period_number", each
[ a = Date.StartOfMonth([date_short]), //Start of month
b = Date.DayOfWeek(a, Day.Monday), //Day of week Start of month
c = Date.AddDays(a, if b = 0 then 0 else 7 - b), //Fist Monday in current month
d = Date.Month([date_short]), //month
e = if [date_short] >= c then d else d - 1
][e], Int64.Type),
periodNameColumn = Table.AddColumn(periodNumberColumn, "period_name", each "P" & Text.PadStart(Text.From([period_number]), 2, "0"), type text),
finalTable = Table.SelectColumns(periodNameColumn, {"date_short", "num_day", "num_month", "num_year", "name_month_br", "name_month_us", "name_month_short_br", "name_month_short_us","num_weekday", "name_weekday_short_br", "name_weekday_br", "name_weekday_short2_br", "name_weekday_us", "name_weekday_short_us", "week_number", "week_name", "period_number", "period_name"})
in
finalTable
Hi @jcnascime, I've updated few steps:
let
startDate = #date(2024, 1, 1),
endDate = #date(Date.Year(DateTime.LocalNow()), 12, 31),
numDays = Duration.From(endDate - startDate),
dateList = List.Dates(startDate, Number.From(numDays) + 1, #duration(1, 0, 0, 0)),
dateTable = Table.FromList(dateList, Splitter.SplitByNothing(), type table[date_short=date]),
numDayColumn = Table.AddColumn(dateTable, "num_day", each Date.Day([date_short]), Int64.Type),
numMonthColumn = Table.AddColumn(numDayColumn, "num_month", each Date.Month([date_short]), Int64.Type),
numYearColumn = Table.AddColumn(numMonthColumn, "num_year", each Date.Year([date_short]), Int64.Type),
nameMonthBrColumn = Table.AddColumn(numYearColumn, "name_month_br", each Date.ToText([date_short], "MMMM"), type text),
nameMonthUsColumn = Table.AddColumn(nameMonthBrColumn, "name_month_us", each Date.ToText([date_short], "MMMM", "en-US"), type text),
nameMonthShortBrColumn = Table.AddColumn(nameMonthUsColumn, "name_month_short_br", each Text.Start([name_month_br], 3), type text),
nameMonthShortUsColumn = Table.AddColumn(nameMonthShortBrColumn, "name_month_short_us", each Text.Start([name_month_us], 3), type text),
numWeekdayColumn = Table.AddColumn(nameMonthShortUsColumn, "num_weekday", each Date.DayOfWeek([date_short], Day.Monday), Int64.Type),
nameWeekdayShortBrColumn = Table.AddColumn(numWeekdayColumn, "name_weekday_short_br", each Text.Start(Date.ToText([date_short], "dddd"), 3), type text),
nameWeekdayBrColumn = Table.AddColumn(nameWeekdayShortBrColumn, "name_weekday_br", each Date.ToText([date_short], "dddd"), type text),
nameWeekdayShort2BrColumn = Table.AddColumn(nameWeekdayBrColumn, "name_weekday_short2_br", each Text.Start([name_weekday_br], 3), type text),
nameWeekdayUsColumn = Table.AddColumn(nameWeekdayShort2BrColumn, "name_weekday_us", each Date.ToText([date_short], "dddd", "en-US"), type text),
nameWeekdayShortUsColumn = Table.AddColumn(nameWeekdayUsColumn, "name_weekday_short_us", each Text.Start([name_weekday_us], 3), type text),
weekNumberColumn = Table.AddColumn(nameWeekdayShortUsColumn, "week_number", each Date.WeekOfYear([date_short], Day.Monday), Int64.Type),
weekNameColumn = Table.AddColumn(weekNumberColumn, "week_name", each "W" & Text.PadStart(Text.From([week_number]), 2, "0"), type text),
periodNumberColumn = Table.AddColumn(weekNameColumn, "period_number", each
[ a = Date.StartOfMonth([date_short]), //Start of month
b = Date.DayOfWeek(a, Day.Monday), //Day of week Start of month
c = Date.AddDays(a, if b = 0 then 0 else 7 - b), //Fist Monday in current month
d = Date.Month([date_short]), //month
e = if [date_short] >= c then d else d - 1
][e], Int64.Type),
periodNameColumn = Table.AddColumn(periodNumberColumn, "period_name", each "P" & Text.PadStart(Text.From([period_number]), 2, "0"), type text),
finalTable = Table.SelectColumns(periodNameColumn, {"date_short", "num_day", "num_month", "num_year", "name_month_br", "name_month_us", "name_month_short_br", "name_month_short_us","num_weekday", "name_weekday_short_br", "name_weekday_br", "name_weekday_short2_br", "name_weekday_us", "name_weekday_short_us", "week_number", "week_name", "period_number", "period_name"})
in
finalTable