Forum Discussion
Solved
Creating month tabe
Hi everyone, I need to create a month table as in starting from Jan-2019 to Dec-2019.Only those 12 rows should be there. I tried calendar function but it gave all the 365 days. I only need the mo...
Hi KJay ,
I think you could create the calendar table with Power Query like below.
let StartDate= #date(2019,1,1), // Change start date #date(yyyy,m,d) EndDate = #date(2019,12,31), // Could change to #date(yyyy,m,d) if you need to specify future date DateList = List.Dates(StartDate, Number.From(EndDate)- Number.From(StartDate)+1 ,#duration(1,0,0,0)), #"Converted to Table" = Table.FromList(DateList, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Named as Date" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}), #"Changed Type" = Table.TransformColumnTypes(#"Named as Date",{{"Date", type date}}), #"Long Month Name" = Table.AddColumn(#"Changed Type", "Month Long", each Date.MonthName([Date]), type text), #"Removed Duplicates" = Table.Distinct(#"Long Month Name", {"Month Long"}) in #"Removed Duplicates"Here is the output.
Then you could show the date format like this in power bi.
Best Regards,
Cherry
Thank you for your reply. I tried this earlier. But it gives the output as a text. I'm unabe to make relationship with another table which actually have dates.
Hi KJay ,
I think you could create the calendar table with Power Query like below.
let
StartDate= #date(2019,1,1), // Change start date #date(yyyy,m,d)
EndDate = #date(2019,12,31), // Could change to #date(yyyy,m,d) if you need to specify future date
DateList = List.Dates(StartDate, Number.From(EndDate)- Number.From(StartDate)+1 ,#duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(DateList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Named as Date" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Named as Date",{{"Date", type date}}),
#"Long Month Name" = Table.AddColumn(#"Changed Type", "Month Long", each Date.MonthName([Date]), type text),
#"Removed Duplicates" = Table.Distinct(#"Long Month Name", {"Month Long"})
in
#"Removed Duplicates"
Here is the output.
Then you could show the date format like this in power bi.
Best Regards,
Cherry