Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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
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
Hello,
it took me some time, but it works.
Thanks a lot.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |