Fix Custom Date Column....PowerBI nerds (jk I love you all) i'm super stuck on what seems so easy

[boldwake]

I have a dashboard tha thas a very extensive calendar table with 30 columns to give me ISO dates, Calendar Dates, different permutaitons etc etc and I built it using a formula to make my start and end dates of the calendar table dynammic based on the date columns in a sales table so that it autorefreshed.  I have now build probably 100 measures and have 8 tables connected and finished all my visualizatoins...

Bottom line a good 100 hours in to this I realized that my calendar table has a row for every day in between the min and max dates defined by the calendar field in my sales table.

1. the calendar table was built using a formula and the "new" table icon under "Modeling" so i can't edit any query

2 . the table does not pull from a text file it was all created in Power BI itself so i can't just update the text file.

3. i realized today that i needed to change my etl data pull to aggregate every saturday so i know have weekly data but my calendar table has daily data. 

Is there any way to change my column in my calendar table to just give me all the saturdays for a two year period?

instead of my rows being:

01/02/2015

01/03/2015

01/04/2015

i want

01/02/2015

01/09/2015

01/16/2015

i ahve tried searching everywhere and i cant figure out a way to simply replace this single column to show only satrudays on every row....

I love love love power bi but right now i miss excel so much...it would be half a millisecond fix vs. redoing 2 solid weeks of work, which i am starting to think i will need to do....

hopefully i'm an idiot and one of you Meta Users has a stupidly simple fix.....

Thank you

[Baskar · Accepted solution]

Create Date Master using the below power Query .

-----------------------------------------------------------

let
Source = (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>
let
DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
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, "Year", each Date.Year([Date])),
InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),
InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", 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(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM", Culture), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & "'" & Text.Range(Number.ToText([Year]),2,2)),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])),
InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd", Culture), type text),
InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date),
InsertWeekStarting = Table.AddColumn(InsertWeekEnding, "WeekStart", each Date.StartOfWeek([Date]), type date)
in
InsertWeekStarting,
#"Invoked FunctionSource" = Source(#date(2015, 1, 1), #date(2016, 12, 31), null),
#"Changed Type" = Table.TransformColumnTypes(#"Invoked FunctionSource",{{"MonthOfYear", Int64.Type}, {"QuarterOfYear", Int64.Type}, {"DayOfMonth", Int64.Type}, {"DayInWeek", Int64.Type}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Date", "Date - Copy"),
#"Calculated Week of Year" = Table.TransformColumns(#"Duplicated Column",{{"Date - Copy", Date.WeekOfYear}}),
#"Renamed Columns" = Table.RenameColumns(#"Calculated Week of Year",{{"Date - Copy", "WeekofMonth"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Year", Int64.Type}, {"DateInt", Int64.Type}, {"MonthInCalendar", type text}, {"QuarterInCalendar", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Sorting Order", each [Year]*100 + [MonthOfYear]),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each true),
#"Changed Type2" = Table.TransformColumnTypes(#"Filtered Rows",{{"Sorting Order", Int64.Type}}),
#"Inserted Quarter" = Table.AddColumn(#"Changed Type2", "Quarter", each Date.QuarterOfYear([Date]), type number),
#"Changed Type3" = Table.TransformColumnTypes(#"Inserted Quarter",{{"Quarter", Int64.Type}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type3", each ([DayOfWeekName] = "Saturday"))
in
#"Filtered Rows1"

it will solve your Problem dude. Cheers