Forum Discussion

KJay's avatar
KJay
Regular Visitor
6 years ago
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...
Need Help
  • v-piga-msft's avatar
    v-piga-msft
    6 years ago

    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

KJay's avatar
KJay
Regular Visitor
6 years ago

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.

amitchandak's avatar
amitchandak
Super User
6 years ago

In case you have tables both month and date level. Please create a field with month start or end date based on month and year and join with the time dimension having all dates.

In case everything is at month level then you create a month start date or month-end date and join with them. Both fact and dim can dates or varchar month format which matches 

 

Month Date = date(MonthTable[Year],MonthTable[Month Number],"01")
Month End Date = EOMONTH(date(MonthTable[Year],MonthTable[Month Number],"01"),0)