Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
39 | |
24 | |
21 | |
21 | |
13 |