Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Floriankx
Solution Sage
Solution Sage

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.

 

1 ACCEPTED SOLUTION
v-yulgu-msft
Employee
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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yulgu-msft
Employee
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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello, 

 

it took me some time, but it works.

 

Thanks a lot.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.