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 ,
Have you solved your problem?
If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
If you still need help, please feel free to ask.
Best Regards,
Cherry