DAX create table of time to get each date of the yeay

[Dom585]

Super new Power BI user. Thanks for the help and apologies for the new guy questions.

I try to create a table of time with all days of the years from a start date to today but I can't find how to fix my error in the DAX below, I get error message :  Expression.SyntaxError : Jeton Comma attendu.

Please help , 

let
Source = List.Dates(StartDate, Length, #duration(1, 0, 0, 0)),
#"Converted to Table" = Table.FromList(Source, Splitter.Sp1itByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}},
#"Changed Type" = Table.TransformColumnType(#"Renamed Columns", {{"Date", type date)}),
// pour changer la date de début, modifiez le code ci-dessous
// par exemple, un début au 1er mars 2020 s'écrit #date(2020, 3, 1)
StartDate = #date(2018, 1, 1),
Today = DateTime.Date(DateTime.LocalNow()),
// la mention Today créé un calendrier courant jusqu'à aujourd'hui
Length = Duration.Days(Today - StartDate),
Custom1 = #"Changed Type",
// Année fiscale commencant au ler aoüt
#"Inserted Year" Table.AddColumn(CustomI, "Année fisc" each Date.Year([Date]+#duration(184,0,0,0)), Int64.Type),
#"lnserted Month Name" = Table.AddColumn(#"Inserted Year", "Nom du mois" each Date.MonthName([Date]), type text),
#"lnserted Day Name" = Table.AddColumn(#"Inserted Month Name", "Nom du jour", each Date.DayOfWeekName([Date]), type text),
#"lnserted Month" = Table.AddColumn(#"Inserted Day Name", "Mois fisc" each if Date.Month([Date]) >=7 then Date.Month([Date])-6 else Date.Month([Date])+6, Int64.Type),
// numéro du jour, pour une semaine commencant le lundi
// l'ajout +1 la fonction DayOfWeek permet au lundi d'être numéroté 1 (au lieu du 0 par défaut)
#"Inserted Day of Week" = Table.AddColumn(#"Inserted Month", "Num du jour", each Date.DateOfWeek([Date]), Day.Monday)+1, Int64.Type),
#"lnserted First Characters" = Table.AddColumn(#"Inserted Day of Week", "MMM", each Text.Start([Nom du mois], 3), type text),
#"Inserted First Charactersl" = Table.AddColumn(#"Inserted First Characters", "JJJ" each Text.Start[Nom du jour], 3), type text),
#"Reordered Columns" = Table.ReorderColumn(#"Inserted First Character1",{"Date", "Année fisc", "Nom du mois", "MMM", "Mois fisc", "Nom du jour", "JJJ", "Num du jour"}),
#"Added Customl" = Table.AddColumn(#"Reordered Columns", "ID mois", each (Date.Year([Date]) - Date.Year(StartDate))*12 + Date.Month([Date])),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1", {{"ID Mois", Int64.Type}})
in
#"Changed Type2"

[v-angzheng-msft]

Hi, @Dom585 

May I ask if your problem has been solved? Is the above post helpful to you?

Here is a piece of code to create a calendar table that may be useful to you:

(optional Start_Year as number,

optional End_Year as number)=>
let
x = Start_Year,
y = if End_Year = null

then Start_Year else End_Year,
begin_date = if x = null

then #date(Date.Year(DateTime.LocalNow()),1,1)
else #date(x,1,1),
end_date = if y = null then #date(Date.Year(DateTime.LocalNow()),12,31)

else #date(y,12,31),
list = {1..Number.From(end_date)-Number.From(begin_date)+1},
dates = List.Transform( list , (item)=> Date.AddDays(begin_date,item-1) ),
table = Table.TransformColumnTypes(Table.RenameColumns(Table.FromList(dates,
Splitter.SplitByNothing(), null, null, ExtraValues.Error),{{"Column1", "Date"}}),{{"Date", type date}}),
year_id = Table.AddColumn(table,"Year", each Date.Year([Date]), type number),
quarter_name = Table.AddColumn(year_id, "Quarter", each "Q"&Text.From(Date.QuarterOfYear([Date]))),
month_id = Table.AddColumn(quarter_name, "Month", each Text.PadStart(Text.From(Date.Month([Date])),2,"0")),
data_id=Table.AddColumn(month_id,"Day", each Date.Day([Date]), type number),
year_quarter_id = Table.AddColumn(data_id, "YearQuarter", each Text.From([Year])&[Quarter]),
year_month_id = Table.AddColumn(year_quarter_id, "YearMonth", each Date.Year([Date])*100+ Date.Month([Date]), type number),
day_in_week = Table.AddColumn(year_month_id, "WeekNum", each Number.Mod(Date.DayOfWeek([Date])+6,7)+1, type number)
in
day_in_week

Create a blank query, open Advanced Editor and replace the text there with the code.  
Result:

Please refer to the attachment below for details. Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlexisOlson · Accepted solution]

You're missing quite a few commas and a couple of other syntax errors. See the corrections in red below.

let
Source = List.Dates(StartDate, Length, #duration(1, 0, 0, 0)),
#"Converted to Table" = Table.FromList(Source, Splitter.Sp1itByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}},
#"Changed Type" = Table.TransformColumnType(#"Renamed Columns", {{"Date", type date}}),
// pour changer la date de début, modifiez le code ci-dessous
// par exemple, un début au 1er mars 2020 s'écrit #date(2020, 3, 1)
StartDate = #date(2018, 1, 1),
Today = DateTime.Date(DateTime.LocalNow()),
// la mention Today créé un calendrier courant jusqu'à aujourd'hui
Length = Duration.Days(Today - StartDate),
Custom1 = #"Changed Type",
// Année fiscale commencant au ler aoüt
#"Inserted Year" = Table.AddColumn(CustomI, "Année fisc", each Date.Year([Date]+#duration(184,0,0,0)), Int64.Type),
#"lnserted Month Name" = Table.AddColumn(#"Inserted Year", "Nom du mois", each Date.MonthName([Date]), type text),
#"lnserted Day Name" = Table.AddColumn(#"Inserted Month Name", "Nom du jour", each Date.DayOfWeekName([Date]), type text),
#"lnserted Month" = Table.AddColumn(#"Inserted Day Name", "Mois fisc", each if Date.Month([Date]) >=7 then Date.Month([Date])-6 else Date.Month([Date])+6, Int64.Type),
// numéro du jour, pour une semaine commencant le lundi
// l'ajout +1 la fonction DayOfWeek permet au lundi d'être numéroté 1 (au lieu du 0 par défaut)
#"Inserted Day of Week" = Table.AddColumn(#"Inserted Month", "Num du jour", each Date.DateOfWeek([Date]), Day.Monday)+1, Int64.Type),
#"lnserted First Characters" = Table.AddColumn(#"Inserted Day of Week", "MMM", each Text.Start([Nom du mois], 3), type text),
#"Inserted First Charactersl" = Table.AddColumn(#"Inserted First Characters", "JJJ", each Text.Start([Nom du jour], 3), type text),
#"Reordered Columns" = Table.ReorderColumn(#"Inserted First Character1",{"Date", "Année fisc", "Nom du mois", "MMM", "Mois fisc", "Nom du jour", "JJJ", "Num du jour"}),
#"Added Customl" = Table.AddColumn(#"Reordered Columns", "ID mois", each (Date.Year([Date]) - Date.Year(StartDate))*12 + Date.Month([Date])),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1", {{"ID Mois", Int64.Type}})
in
#"Changed Type2"