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)

       

       

    • v-piga-msft's avatar
      v-piga-msft
      Resident Rockstar
      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

    • v-piga-msft's avatar
      v-piga-msft
      Resident Rockstar
      6 years ago

      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