Last 50 Posts on Chandoo.org

Jim Cruse on 2023-09-28 23:51:31
I cannot get the fourth Equation of TURE or FALSE statements to work in my excel spreadsheet You have =($E$2:$G$25=0)+($E$2:$G$25=1)=1 as a SUMIF solution, I have, =($F$2:$H$13=0)+($F$2:$H$13=1)=1 as my solution but it does not work. The only thing I changed is the ranges. Any suggestions? Sorry I left some of it out in the previous question, Thank you. Jim

Jim Cruse on 2023-09-28 23:47:25
I cannot get the fourth Equation to work in my excel spreadsheet You have =($E$2:$G$25=0)+($E$2:$G$25=1)=1 as a SUMIF solution, I have, =($F$2:$H$13=0)+($F$2:$H$13=1)=1 as my solution but it does not work. The only thing I changed is the ranges. Any suggestions? Thank you. Jim

Jay Vee on 2023-09-28 20:32:07
Just when I think I know a lot about Excel, I read this and realize I have barely scratched the surface. Thank you Chandoo for this content!

Chandoo on 2023-09-27 05:18:28
You can try the approach outlined here - https://chandoo.org/wp/combine-excel-files-using-power-query/

James on 2023-09-26 12:19:52
i have 4 excel files. Each of these files have two sheets, one named "Branch" and the other named "HO". I want to upload these files in power bi but i want to combine all the sheets named "Branch" into one power bi table, and the other sheet named "HO" into another power bi table

Spark Bailey on 2023-09-25 22:17:34
Chandoo really knows how to 'Excel'erate our understanding of spreadsheets, turning cells into storytelling canvases. His tutorials are not just about numbers, they're about creating 'formula's for success!

Ludger Boecken on 2023-09-25 14:10:27
Hi there, for some reason the file works great iwith US zip codes, unless they start with a "0", like NJ or New England. The the calculator spits out zeros for distance and driving time, no matter what the zip codes are. I formatted the zip codes a s zip codes in Excel, not as text. This way they should be recognized as number and still show the "0" at the beginning. Any idea what the problem is? Ludger

Zara on 2023-09-23 22:22:13
Hi, Thank you this is great stuff and really useful. As well as department as demonstrated on your clip, how can I display all candidates on the grid at once? Many thanks in advance

Seema on 2023-09-21 11:09:48
I have a list of 150 employeees who have to be rewarded, but each slab is applicable to selected no. of employess like 60% slab is applicable to only top 5 employees and after that next slab is applicable irrespective of Avg%, how can I use vlook up in this case. Avg % Amount no. of persons eligible per slab 35% 1,000 10 40% 1,500 5 45% 2,000 5 47% 2,500 5 50% 3,500 5 55% 3,800 5 60% 4,000 5

AQ on 2023-09-18 10:41:06
Great and a fantastic short cut which I've found after all these years. Unfortunately, my Logitech MX Keyboard, doesn't have the dedicated shortcut key.

Megan on 2023-09-16 18:59:02
Love this technique, it's very well-organized and tidy. However, I do have a concern that when using a manually created table for discounts, there might be errors or instances of repetition creeping in. Additionally, this method doesn't account for changes in discounts over time. Assuming the original data set included dates, having start dates would be beneficial for looking up the applicable discount.

Pranav Dave on 2023-09-14 10:43:05
=VLOOKUP(TRIM(CLEAN(MID(B4,FIND("x",B4)-4,3))),$H$4:$I$9,2,0)

Chandoo on 2023-09-10 20:50:18
Updated on 11-Sep: I fixed the problem with "interest saved" calculation. Turns out there was an internal bug in cumipmt function in Excel that reports incorrect values. Something new! Please use the link above to re-download the correct file.

Chandoo on 2023-09-10 20:35:45
I too realized this problem. I will post an update once I fix the error.

Ubhay shankar Shastri on 2023-09-10 06:18:22
I facing some problem in importing data ...the error looks as failed to load with error exitcode 1..please reply

Chandresh Mehta on 2023-09-08 16:58:55
Hi Chandoo, I am a big fan of your dashboards and reports, i take lot of ideas from it to build my project management dashboards. Regarding Benefits Analysis, could you please help provide information in details on how to calculate benefits realization for 5 years for a IT Software development project? it can be both tangible and intangible benefits

Vince on 2023-09-08 11:25:21
This is great, thanks a lot! I think there is an error in the "You save X $ in interest". If i dont put any extra payment, it still says that I will save 19k in interest rate.

Chike Chika Oguoma on 2023-08-29 10:58:44
Hi Chandoo, Concerning the Download distance calculator template, i have the following questions and i wish you will answer them in this order: 1. Where exactly am I supposed to insert my bing maps API key? 2. What is the earliest version of microsoft excel that this formula works in? 3. Does it generate the distance matrix on its own or do i have to do anything else to generate the entire distance matrix? 4. If I have to generate the driving distance matrix myself, how do i do that? Thank you helping.

Praneel Matai on 2023-08-29 10:47:38
Hi Chandoo. Great channel. Below is the one that I use and I have carried and adapted this from other BI tools I have used in the past. Some additional value and flexibility: let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i44FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = let fnDateTable = (StartDate as date, EndDate as date, optional FYStartMonthNum as number, optional Holidays as list, optional WDStartNum as number ) as table => let FYStartMonth = if List.Contains( {1..12}, FYStartMonthNum ) then FYStartMonthNum else 1, WDStart = if List.Contains( {0, 1}, WDStartNum ) then WDStartNum else 0, CurrentDate = Date.From(DateTime.FixedLocalNow()), DayCount = Duration.Days(Duration.From(EndDate - StartDate))+1, Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)), AddToday = if EndDate < CurrentDate then List.Combine( {Source, {CurrentDate}}) else Source, TableFromList = Table.FromList(AddToday, Splitter.SplitByNothing()), ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}), RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}), InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]), type number), InsertYearOffset = Table.AddColumn(InsertYear, "YearOffset", each Date.Year([Date]) - Date.Year(Date.From(CurrentDate)), type number), InsertCompletedYear = Table.AddColumn(InsertYearOffset, "YearCompleted", each Date.EndOfYear([Date]) < Date.From(Date.EndOfYear(CurrentDate)), type logical), InsertQuarter = Table.AddColumn(InsertCompletedYear, "QuarterOfYear", each Date.QuarterOfYear([Date]), type number), InsertCalendarQtr = Table.AddColumn(InsertQuarter, "Quarter & Year", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year]), type text), InsertQuarternYear = Table.AddColumn(InsertCalendarQtr, "QuarternYear", each [Year] * 10000 + [QuarterOfYear] * 100, type number), InsertQuarterOffset = Table.AddColumn(InsertQuarternYear, "QuarterOffset", each ((4 * Date.Year([Date])) + Date.QuarterOfYear([Date])) - ((4 * Date.Year(Date.From(CurrentDate))) + Date.QuarterOfYear(Date.From(CurrentDate))), type number), InsertCompletedQuarter = Table.AddColumn(InsertQuarterOffset, "QuarterCompleted", each Date.EndOfQuarter([Date]) < Date.From(Date.EndOfQuarter(CurrentDate)), type logical), InsertMonth = Table.AddColumn(InsertCompletedQuarter, "MonthOfYear", each Date.Month([Date]), type number), InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date]), type number), InsertMonthName = Table.AddColumn(InsertDay, "Month Name", each Text.Proper( Date.ToText([Date], "MMMM")), type text), InsertMonthShort = Table.AddColumn( InsertMonthName, "MonthShortName", each try Text.Proper( Text.Start([Month Name], 3 )) otherwise Text.Proper( [Month Name] ), type text), InsertMonthInitial = Table.AddColumn(InsertMonthShort, "Month Initial", each Text.Proper(Text.Start([Month Name], 1)) & Text.Repeat( Character.FromNumber(8203), [MonthOfYear] ), type text), InsertCalendarMonth = Table.AddColumn(InsertMonthInitial, "Month & Year", each [MonthShortName] & " " & Number.ToText([Year]), type text), InsertMonthnYear = Table.AddColumn(InsertCalendarMonth , "MonthnYear", each [Year] * 10000 + [MonthOfYear] * 100, type number), InsertMonthOffset = Table.AddColumn(InsertMonthnYear, "MonthOffset", each ((12 * Date.Year([Date])) + Date.Month([Date])) - ((12 * Date.Year(Date.From(CurrentDate))) + Date.Month(Date.From(CurrentDate))), type number), InsertCompletedMonth = Table.AddColumn(InsertMonthOffset, "MonthCompleted", each Date.EndOfMonth([Date]) < Date.From(Date.EndOfMonth(CurrentDate)), type logical), InsertMonthEnding = Table.AddColumn(InsertCompletedMonth, "MonthEnding", each Date.EndOfMonth([Date]), type date), InsertDayInt = Table.AddColumn(InsertMonthEnding, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth], type number), InsertDayOfYear = Table.AddColumn(InsertDayInt, "Day of Year", each Date.DayOfYear([Date]), Int64.Type), InsertDayWeek = Table.AddColumn(InsertDayOfYear, "DayOfWeek", each Date.DayOfWeek([Date], Day.Monday) + WDStart, Int64.Type), InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Text.Proper( Date.ToText([Date], "dddd" )), type text), InsertDayInitial = Table.AddColumn(InsertDayName, "Weekday Initial", each Text.Proper(Text.Start([DayOfWeekName], 1)) & Text.Repeat( Character.FromNumber(8203), [DayOfWeek] ), type text), InsertWeekNumber= Table.AddColumn(InsertDayInitial, "ISO Weeknumber", each if Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7)=0 then Number.RoundDown((Date.DayOfYear(#date(Date.Year([Date])-1,12,31))-(Date.DayOfWeek(#date(Date.Year([Date])-1,12,31), Day.Monday)+1)+10)/7) else if (Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7)=53 and (Date.DayOfWeek(#date(Date.Year([Date]),12,31), Day.Monday)+139 then 4 else if [ISO Weeknumber] >26 then 3 else if [ISO Weeknumber] >13 then 2 else 1, Int64.Type), InsertISOqtr = Table.AddColumn(InsertISOqNum, "ISO Quarter", each "Q" & Number.ToText([ISO QuarterOfYear]), type text), InsertISOQuarter = Table.AddColumn(InsertISOqtr, "ISO Quarter & Year", each "Q" & Number.ToText([ISO QuarterOfYear]) & " " & Number.ToText([ISO Year]), type text), InsertISOqNy = Table.AddColumn(InsertISOQuarter, "ISO QuarternYear", each [ISO Year] * 10000 + [ISO QuarterOfYear] * 100, type number), //InsertISOday = Table.AddColumn(InsertISOqNy, "ISO Day of Year", (OT) => Table.RowCount( Table.SelectRows( BufferTable, (IT) => IT[DateInt] <= OT[DateInt] and IT[ISO Year] = OT[ISO Year])), Int64.Type), InsertCalendarWk = Table.AddColumn(InsertISOqNy, "Week & Year", each Text.From([ISO Year]) & "-" & Text.PadStart( Text.From( [ISO Weeknumber] ), 2, "0"), type text ), InsertWeeknYear = Table.AddColumn(InsertCalendarWk, "WeeknYear", each [ISO Year] * 10000 + [ISO Weeknumber] * 100, Int64.Type), InsertWeekOffset = Table.AddColumn(InsertWeeknYear, "WeekOffset", each (Number.From(Date.StartOfWeek([Date], Day.Monday))-Number.From(Date.StartOfWeek(CurrentDate, Day.Monday)))/7, type number), InsertCompletedWeek = Table.AddColumn(InsertWeekOffset, "WeekCompleted", each Date.EndOfWeek( [Date], Day.Monday) = FYStartMonth and FYStartMonth >1 then Text.PadEnd( Text.End( Text.From([Year] +1), 2), 2, "0") else Text.End( Text.From([Year]), 2)), type text), AddFQ = Table.AddColumn(AddFY, "Fiscal Quarter", each "FQ" & Text.From( Number.RoundUp( Date.Month( Date.AddMonths( [Date], - (FYStartMonth -1) )) / 3 )), type text), AddFQnYr = Table.AddColumn(AddFQ, "FQuarternYear", each (if [MonthOfYear] >= FYStartMonth and FYStartMonth >1 then [Year] +1 else [Year]) * 10000 + Number.RoundUp( Date.Month( Date.AddMonths( [Date], - (FYStartMonth -1) )) / 3 ) * 100, type number), AddFM = Table.AddColumn(AddFQnYr, "Fiscal Period", each if [MonthOfYear] >= FYStartMonth and FYStartMonth >1 then [MonthOfYear] - (FYStartMonth-1) else if [MonthOfYear] >= FYStartMonth and FYStartMonth =1 then [MonthOfYear] else [MonthOfYear] + (12-FYStartMonth+1), type text), AddFMnYr = Table.AddColumn(AddFM , "FPeriodnYear", each (if [MonthOfYear] >= FYStartMonth and FYStartMonth >1 then [Year] +1 else [Year]) * 10000 + [Fiscal Period] * 100, type number), FYCalendarStart = #date( Date.Year(StartDate)-1, FYStartMonth, 1 ), InsertFFD = Table.AddColumn( AddFMnYr, "FiscalFirstDay", each if [MonthOfYear] >= FYStartMonth and FYStartMonth >1 then #date( Date.Year([Date])+1, FYStartMonth, 1) else #date( Date.Year([Date]), FYStartMonth, 1) ), AddFYDateRange = Table.Buffer( Table.ExpandTableColumn( Table.ExpandTableColumn( Table.AddColumn( Table.Group( Table.Group( Table.AddColumn( Table.AddColumn( Table.RenameColumns( Table.TransformColumnTypes( Table.FromList( { Number.From(FYCalendarStart)..Number.From(EndDate) }, Splitter.SplitByNothing()),{{"Column1", type date}}), {{"Column1", "Date"}}), "FiscalFirstDay", each if Date.Month([Date]) < FYStartMonth then #date( Date.Year([Date]), FYStartMonth, 1) else #date( Date.Year([Date])+1, FYStartMonth, 1)), "FWStartDate", each Date.AddYears(Date.StartOfWeek( [Date], Day.Monday), 1)), {"FiscalFirstDay", "FWStartDate"}, {{"AllRows", each _, type table [Date=nullable date, FiscalFirstDay=date, FWStartDate=date]}}), {"FiscalFirstDay"}, {{"AllRows2", each _, type table [FiscalFirstDay=date, FWStartDate=date, AllRows=table]}}), "Custom", each Table.AddIndexColumn( [AllRows2], "FY Week", 1, 1))[[Custom]], "Custom", {"FiscalFirstDay", "FWStartDate", "AllRows", "FY Week"}, {"FiscalFirstDay", "FWStartDate", "AllRows", "FY Week"}), "AllRows", {"Date"}, {"Date"})[[Date], [FY Week]] ), MergeFYW = Table.NestedJoin(InsertFFD, {"Date"}, AddFYDateRange, {"Date"}, "AddFYWeek", JoinKind.LeftOuter), ExpandFYWeek = Table.TransformColumnTypes( Table.ExpandTableColumn(MergeFYW, "AddFYWeek", {"FY Week"}, {"Fiscal Week"}),{{"Fiscal Week", Int64.Type}}), UpdateFYWeek = if FYStartMonthNum =null then Table.ReplaceValue(ExpandFYWeek, each [Fiscal Week], each if FYStartMonth =1 then [#"ISO Weeknumber"] else [Fiscal Week],Replacer.ReplaceValue,{"Fiscal Week"}) else ExpandFYWeek, AddFYW = Table.AddColumn( UpdateFYWeek, "Fiscal Year & Week", each if FYStartMonthNum =null then [#"Week & Year"] else if Date.Month([Date]) < FYStartMonth then Text.From( Date.Year([Date])) & "-" & Text.PadStart( Text.From([Fiscal Week]), 2, "0") else Text.From( Date.Year([Date])+1) & "-" & Text.PadStart(Text.From([Fiscal Week]), 2, "0"), type text), InsertFWeeknYear = Table.AddColumn(AddFYW, "FWeeknYear", each if FYStartMonthNum =null then [WeeknYear] else (if Date.Month([Date]) < FYStartMonth then Date.Year([Date]) else Date.Year([Date])+1) * 10000 + [Fiscal Week] * 100, Int64.Type), InsertIsAfterToday = Table.AddColumn(InsertFWeeknYear, "IsAfterToday", each not ([Date] 4 then false else true, type logical), InsertIsHoliday = Table.AddColumn(InsertIsWorkingDay, "IsHoliday", each if Holidays = null then "Unknown" else List.Contains( Holidays, [Date] ), if Holidays = null then type text else type logical), InsertIsBusinessDay = Table.AddColumn(InsertIsHoliday, "IsBusinessDay", each if [IsWorkingDay] = true and [IsHoliday] true then true else false, type logical), InsertDayType = Table.AddColumn(InsertIsBusinessDay, "Day Type", each if [IsHoliday] = true then "Holiday" else if [IsWorkingDay] = false then "Weekend" else if [IsWorkingDay] = true then "Weekday" else null, type text), CurrentDateRecord = Table.SelectRows(InsertDayType, each ([Date] = CurrentDate)), CurrentISOyear = CurrentDateRecord{0}[ISO Year], CurrentISOqtr = CurrentDateRecord{0}[ISO QuarterOfYear], CurrentYear = CurrentDateRecord{0}[Year], CurrentMonth = CurrentDateRecord{0}[MonthOfYear], CurrentFiscalFirstDay = CurrentDateRecord{0}[FiscalFirstDay], PrevFiscalFirstDay = Date.AddYears(CurrentFiscalFirstDay, -1), CurrentFQ = CurrentDateRecord{0}[FQuarternYear], CurrentFP = CurrentDateRecord{0}[FPeriodnYear], CurrentFW = CurrentDateRecord{0}[FWeeknYear], InsertISOQtrOffset = Table.AddColumn(InsertDayType, "ISO QuarterOffset", each ((4 * [ISO Year]) + [ISO QuarterOfYear]) - ((4 * CurrentISOyear) + CurrentISOqtr), type number), InsertISOYrOffset = Table.AddColumn(InsertISOQtrOffset, "ISO YearOffset", each [ISO Year] - CurrentISOyear, type number), InsertFYoffset = Table.AddColumn(InsertISOYrOffset, "FiscalYearOffset", each try (if [MonthOfYear] >= FYStartMonth then [Year]+1 else [Year]) - (if CurrentMonth >= FYStartMonth then CurrentYear+1 else CurrentYear) otherwise null, type number), InsertCurrentFQ = Table.AddColumn(InsertFYoffset, "IsCurrentFQ", each if [FQuarternYear] = CurrentFQ then true else false, type logical), InsertCurrentFP = Table.AddColumn(InsertCurrentFQ, "IsCurrentFP", each if [FPeriodnYear] = CurrentFP then true else false, type logical), InsertCurrentFW = Table.AddColumn(InsertCurrentFP, "IsCurrentFW", each if [FWeeknYear] = CurrentFW then true else false, type logical), InsertPYTD = Table.AddColumn(InsertCurrentFW, "IsPYTD", each if CurrentYear-1 = [Year] and [Day of Year] <= CurrentDateRecord{0}[Day of Year] then true else false, type logical), ListPrevFYDates = List.Buffer( Table.SelectRows( Table.ExpandTableColumn( Table.NestedJoin( Table.AddIndexColumn( Table.RenameColumns( Table.TransformColumnTypes( Table.FromList( List.Dates( PrevFiscalFirstDay, Number.From(CurrentFiscalFirstDay-PrevFiscalFirstDay),#duration(1,0,0,0)), Splitter.SplitByNothing()),{{"Column1", type date}}), {{"Column1", "DateFY"}}), "Index", 1, 1), {"Index"}, Table.AddIndexColumn( Table.RenameColumns( Table.TransformColumnTypes( Table.FromList( List.Dates( Date.AddYears( PrevFiscalFirstDay, -1), Number.From( PrevFiscalFirstDay - Date.AddYears( PrevFiscalFirstDay, -1)),#duration(1,0,0,0)), Splitter.SplitByNothing()),{{"Column1", type date}}), {{"Column1", "DateFY"}}), "Index", 1, 1) , {"Index"}, "Table", JoinKind.LeftOuter), "Table", {"DateFY"}, {"PrevDateFY"}), each [DateFY] <= CurrentDate)[PrevDateFY] ), InsertPFYTD = Table.AddColumn(InsertPYTD, "IsPFYTD", each if [FiscalYearOffset] = -1 and List.Contains(ListPrevFYDates, [Date] ) then true else false, type logical), RemoveToday = Table.RemoveColumns( if EndDate < CurrentDate then Table.SelectRows(InsertPFYTD, each ([Date] CurrentDate)) else InsertPFYTD, {"Day of Year", "FiscalFirstDay"}), ChType = Table.TransformColumnTypes(RemoveToday,{{"Year", Int64.Type}, {"QuarterOfYear", Int64.Type}, {"MonthOfYear", Int64.Type}, {"DayOfMonth", Int64.Type}, {"DateInt", Int64.Type}, {"DayOfWeek", Int64.Type}, {"ISO YearOffset", Int64.Type}, {"ISO QuarternYear", Int64.Type}, {"ISO QuarterOffset", Int64.Type}, {"ISO Weeknumber", Int64.Type}, {"WeeknYear", Int64.Type}, {"MonthnYear", Int64.Type}, {"QuarternYear", Int64.Type}, {"FQuarternYear", Int64.Type}, {"Fiscal Period", Int64.Type}, {"FPeriodnYear", Int64.Type}, {"WeekOffset", Int64.Type}, {"MonthOffset", Int64.Type}, {"QuarterOffset", Int64.Type}, {"YearOffset", Int64.Type}, {"FiscalYearOffset", Int64.Type}}), ReorderColumns = Table.ReorderColumns(ChType, {"Date", "Year", "YearOffset", "YearCompleted", "QuarterOfYear", "Quarter & Year", "QuarternYear", "QuarterOffset", "QuarterCompleted", "MonthOfYear", "DayOfMonth", "Month Name", "MonthShortName", "Month Initial", "Month & Year", "MonthnYear", "MonthOffset", "MonthCompleted", "MonthEnding", "DateInt", "DayOfWeek", "DayOfWeekName", "Weekday Initial", "Day Type", "ISO Year", "ISO YearOffset", "ISO QuarterOfYear", "ISO Quarter", "ISO Quarter & Year", "ISO QuarternYear", "ISO QuarterOffset", "ISO Weeknumber", "Week & Year", "WeeknYear", "WeekOffset", "WeekCompleted", "WeekEnding", "Fiscal Year", "FiscalYearOffset", "Fiscal Quarter", "FQuarternYear", "IsCurrentFQ", "Fiscal Period", "FPeriodnYear", "IsCurrentFP", "Fiscal Week", "Fiscal Year & Week", "FWeeknYear", "IsCurrentFW", "IsAfterToday", "IsWorkingDay", "IsHoliday", "IsBusinessDay", "IsPYTD", "IsPFYTD"}, MissingField.UseNull) in ReorderColumns, Documentation = [ Documentation.Name = " fxCalendar", Documentation.Description = " Date table function to create an ISO-8601 calendar", Documentation.LongDescription = " Date table function to create an ISO-8601 calendar", Documentation.Category = " Table", Documentation.Version = " 1.32: Adjusted fiscal weeks logic depending on wheter a fiscal start month was submitted", Documentation.Source = " local", Documentation.Author = " Melissa de Korte", Documentation.Examples = { [Description = " See: https://forum.enterprisedna.co/t/extended-date-table-power-query-m-function/6390", Code = " Optional paramters: #(lf) (FYStartMonthNum) Month number the fiscal year starts, Januari if omitted #(lf) (Holidays) Select a query (and column) that contains a list of holiday dates #(lf) (WDStartNum) Switch default weekday numbering from 0-6 to 1-7 by entering a 1 #(lf) #(lf) Important to note: #(lf) [Fiscal Week] starts on a Monday and can contain less than 7 days in a First- and/or Last Week of a FY #(lf) [IsWorkingDay] does not take holiday dates into account #(lf) [IsBusinessDay] does take optional holiday dates into account #(lf) [IsPYTD] and [IsPFYTD] compare Previous [Day of Year] with the Current [Day of Year] number, so dates don't align in leap years", Result = " " ] } ] in Value.ReplaceType( fnDateTable, Value.ReplaceMetadata( Value.Type( fnDateTable ), Documentation )) in #"Changed Type"

Chandoo on 2023-08-24 20:48:03
No. The old file shouldn't be impacted.

sil klgt on 2023-08-24 03:02:07
Dear Chandoo, Maybe this question is out of the topic, but I need to know.. If I duplicated a file containing power query and delete it in the new file, will the old file be affected? Thank you!

Chandoo on 2023-08-23 20:43:29
In this case, you can generate the calendar for two years and then use the "year end" feature of any date calculation functions in DAX. I think, you can also add quarter calculations in Power Query with an optional year end parameter.

SALMAN on 2023-08-23 11:55:27
Dear Chandoo, My financial year Calendar starts with July & Ends to June. e/g Finacial Year starts with 01 July 2022 & Ends on 30 June 2023. How can I shape my calendar in this format. Please guide

Rahul on 2023-08-23 09:39:00
Need a little help i am customising the excel and i need that the every 6 tickets rows don't repeat the same no.

Chandoo on 2023-08-22 20:26:00
Thanks Ken for that suggestion. I have now added MonkeyTools link to the article :)

Ken Puls on 2023-08-22 16:59:26
Heya Chandoo, Can I respectfully suggest another resource which (I believe) creates a better calendar table? My Monkey Tools add-in has a "Calendar Monkey" which provides you a no-code interface to generate your Power Query based Calendar table, and which updates from your data so you never need to adjust the start date ever again. While the add-in works in Excel, the code can be copied to (or imported) into Power BI as well, and works there without issue. You can find out more about it at https://monkeytools.ca/calendar-monkey/

Sean H. on 2023-08-15 16:16:09
Great to see you back! Enjoyed when you first started the podcast and super excited to start listening to you again!

Aubrey Meissenheimer on 2023-08-11 17:07:19
Excellent Info Many Thanks What do you have in the line of a Decision Making Matrix ? Aubrey Meissenheimer Pretoria, South Africa

MARIAMA Ndao on 2023-08-11 15:26:51
The calendar template does not work for October. Can you please check it. Thank you

Shivaji Subhash Shingade on 2023-08-07 08:59:46
hi all, i need help in one thing i.e display backstage controls on sheet change event. now very simple how to refresh controls in excel like refresh ribbon.