Forum Discussion

Floriankx's avatar
Floriankx
Solution Sage
8 years ago
Solved

Create Date Table (Power Query) according to Min Max Date, for Power Query experts

Hello together,

 

I think I may have quite a challenge

Given is a table with several dates. I am able to create a querry to get MinDate and MaxDate:

dDateRange:

let
    Source = TableWithDateColumn
    Clear = Table.RemoveColumns(Source,{AllExceptDateColumn}),
    #"Grouped Rows" = Table.Group(Clear, {}, {{"MinDate", each List.Min([Datum]), type date}, {"MaxDate", each List.Max([Datum]), type date}})
in
    #"Grouped Rows"

Then I have a function to create a date according to MinDate and MaxDate

let CreateDateTable = (optional Culture as nullable text) as table =>
  let
    StartDate=dDateRange[MinDate]{0},
    EndDate=dDateRange[MaxDate]{0},
    DayCount = Duration.Days(Duration.From(EndDate - StartDate))+1, //'+1' da Alleebaumproblem
    Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
    TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
    RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
    InsertYear = Table.AddColumn(RenamedColumns, "Jahr", each Date.Year([Date])),
    //InsertQuarter = Table.AddColumn(tbd, "QuarterOfYear", each Date.QuarterOfYear([Date])),
    InsertMonth = Table.AddColumn(InsertYear, "Int_Monat", each Date.Month([Date])),
    //InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),
    //InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
    InsertMonthName = Table.AddColumn(InsertMonth, "Monat", each Date.ToText([Date], "MMM", Culture), type text),
    //InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),
    //InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),
    //InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])),
    //InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date),
    RenamedDate = Table.RenameColumns(InsertMonthName,{{"Date", "Datum"}})
  in
    RenamedDate
in
  CreateDateTable

The function is none of my own development see here.

 

As you can see I don't use all columns the function is able to create.

 

As a last step I have to call the function:

let
    Source = fctDate_v2(null),
in
    Source

This is really good stuff. But it means, I have the function and the dDateRange in my Querries.

 

So to all the Power Query Experts, is there any possibility not to seperately create a dDateRange and maybe even not do display the function permanently? I'm using Excel instead of PowerBI so I want to keep it slim.

 

  • Hi Floriankx,

     

    If you don't want the functions to be displayed separately, you could wrap it in your main M code, like the example in this blog.

     

    Regards,

    Yuliana Gu

2 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee

    Hi Floriankx,

     

    If you don't want the functions to be displayed separately, you could wrap it in your main M code, like the example in this blog.

     

    Regards,

    Yuliana Gu

    • Floriankx's avatar
      Floriankx
      Solution Sage

      Hello, 

       

      it took me some time, but it works.

       

      Thanks a lot.