Date problem

[noorkhan]

I have data in which year, month number and week number and day number of week is mentioned in separate column. how can i combine all these column to get date. 

Thanks

[Anonymous]

Hi @noorkhan ,

Try to add a custom column like

= Number.ToText([Year])&"-"&Number.ToText([MonthNumber])&"-"&Number.ToText(([WeekNumber]-1)*7+[day number])

You can change the type to date.

Best Regards,

ShundaSteph

[Anonymous · Accepted solution]

So your data looks like this, except the Date column?

Is this data your Date table or part of your Facts table?

My quick and dirty solution would be to either merge your data with a Date Table that does contain dates, joining on Year, MonthNumber and Weeknummer. Or, alternatively, create a Date Table from scratch.

Here's the M-code for a Date Table that automatically generates dates for a period of 6 years centered on the current year.

let
    Bron = {Int64.From(Date.StartOfYear(Date.AddYears(Date.From(DateTime.LocalNow()), -3)))..Int64.From(Date.EndOfYear(Date.AddYears(Date.From(DateTime.LocalNow()),3)))},
    #"Geconverteerd naar tabel" = Table.FromList(Bron, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Type gewijzigd" = Table.TransformColumnTypes(#"Geconverteerd naar tabel",{{"Column1", type date}}),
    #"Namen van kolommen gewijzigd" = Table.RenameColumns(#"Type gewijzigd",{{"Column1", "Date"}}),
    #"Aangepaste kolom toegevoegd" = Table.AddColumn(#"Namen van kolommen gewijzigd", "Year", each Date.Year([Date])),
    #"Aangepaste kolom toegevoegd1" = Table.AddColumn(#"Aangepaste kolom toegevoegd", "MonthNumber", each Date.Month([Date])),
    #"Aangepaste kolom toegevoegd2" = Table.AddColumn(#"Aangepaste kolom toegevoegd1", "WeekNummer", each Date.DayOfWeek([Date])),
    #"Type gewijzigd1" = Table.TransformColumnTypes(#"Aangepaste kolom toegevoegd2",{{"Date", type date}, {"Year", Int64.Type}, {"MonthNumber", Int64.Type}, {"WeekNummer", Int64.Type}})
in
    #"Type gewijzigd1"

[SpartaBI]

@noorkhan you don't have the day in the month maybe? :))