Pro tips to optimize Power BI data modeling: automating manual tasks
Context
Recently, I built a semantic model in Power BI that had multiple columns in my fact table. I didn’t want to write simple DAX measures (such as SUM, MAX, AVERAGE, MEDIAN…) manually for each column, change the data format, add a description and so on… if you ever came across this tedious task before, you know what I am talking about. This is just an example. Today I want to share some ideas and techniques that I use in my daily work to automize such manual tasks.
The main purpose of this post is to show you some possible ways to make our life easier during data model developement in Power BI.
In this post we are going to talk about the following topics:
Create multiple DAX measures using Tabular Editor 2
How to format all your DAX measures at once
Create a robust date table for your model
Update Power BI metadata simultaneously
Check referential integrity violation in your data model
Data model documentation
Parameterizing source connections
Best Practice Analyzer (BPA) to improve your model’s performance
Quickly check DAX measure dependencies
Tip 1: Create multiple DAX measures using Tabular Editor 2
A very common approach when writing DAX measures is to write basic DAX measures (SUM, MAX, MIN…) first and then use these measures in your future measures. This is called measure branching.
How to create DAX measures for SUM, MAX, MIN, AVERAGE and MEDIAN at once, including data format and description of the measure?
You can do this using a free external tool called Tabular Editor 2. See below the screenshot from Tabular Editor 2:
You can find the complete C# script used below. You can copy the C# script below in your Tabular Editor.
-
// Creates a SUM, MAX, MIN, AVERAGE, MEDIAN measure for every currently selected column, change data format and add a measure description
foreach(var c in Selected.Columns)
{
var newMeasure = c.Table.AddMeasure(
"Sum of " + c.Name, // Name
"SUM(" + c.DaxObjectFullName + ")", // DAX expression
c.DisplayFolder // Display Folder
);
// Set the format string on the new measure:
newMeasure.FormatString = "0.00";
// Provide some documentation:
newMeasure.Description = "This measure is the sum of column " + c.DaxObjectFullName;
var newMeasure2 = c.Table.AddMeasure(
"Max of " + c.Name, // Name
"MAX(" + c.DaxObjectFullName + ")", // DAX expression
c.DisplayFolder // Display Folder
);
// Set the format string on the new measure:
newMeasure2.FormatString = "0.00";
// Provide some documentation:
newMeasure2.Description = "This measure is the max of column " + c.DaxObjectFullName;
var newMeasure3 = c.Table.AddMeasure(
"Min of " + c.Name, // Name
"MIN(" + c.DaxObjectFullName + ")", // DAX expression
c.DisplayFolder // Display Folder
);
// Set the format string on the new measure:
newMeasure3.FormatString = "0.00";
// Provide some documentation:
newMeasure3.Description = "This measure is the min of column " + c.DaxObjectFullName;
var newMeasure4 = c.Table.AddMeasure(
"Average of " + c.Name, // Name
"AVERAGE(" + c.DaxObjectFullName + ")", // DAX expression
c.DisplayFolder // Display Folder
);
// Set the format string on the new measure:
newMeasure4.FormatString = "0.00";
// Provide some documentation:
newMeasure4.Description = "This measure is the average of column " + c.DaxObjectFullName;
var newMeasure5 = c.Table.AddMeasure(
"Median of " + c.Name, // Name
"MEDIAN(" + c.DaxObjectFullName + ")", // DAX expression
c.DisplayFolder // Display Folder
);
// Set the format string on the new measure:
newMeasure5.FormatString = "0.00";
// Provide some documentation:
newMeasure5.Description = "This measure is the median of column " + c.DaxObjectFullName;
}
Tip 2: How to format all your DAX measures at once?
“If it is not formatted… it is not DAX!”
Imagine you got a data model from someone and the DAX measures are not properly formatted. It would be a tedious task to manually format them. There are 2 ways that I know and use to do this task.
Option 1: Use the external tool Bravo to format all your DAX measures
This option uses the external tool Bravo developed by the SQLBI team. Here you can choose how you would like to format your DAX measures:
See below an example in action transforming an unformatted DAX measure into a formatted one. If you are wondering how Bravo does the formatting, it sends the measures to the DAX Formatter service (also developed by the SQLBI team).
Option 2: Use C# scripts inside of Tabular Editor to format all your DAX measures
If your organization does not allow you to install Bravo, then we could use Tabular Editor to do this task. Just for your information, it is possible to download the zip file from Tabular Editor 2 and use it without having admin rights.
See below the C# script to format all your DAX measures and add a break line all at once:
-
// C# Script to format all DAX measures and add a break line
// Format all DAX measures
Model.AllMeasures.FormatDax();
// Add a break line in the DAX measures
foreach(var measure in Model.AllMeasures)
{
measure.Expression = Environment.NewLine + measure.Expression;
}
The C# script above also uses DAX Formatter to format the DAX measures. For more information about it check the Tabular Editor documentation.
Tip 3: Create a robust date table for your model
A good date table plays a very important role in your semantic model. For example, simplifying time intelligence DAX measures, being able to do time intelligence analysis based on fiscal year and so on. There are many ways to create a date table. Assuming you don’t have a date table in your data warehouse, there are 2 ways that I personally like to create my date table:
Option 1: Write the date table in Power Query with M code
I really like the date table written in M code by Melissa de Korte and this is the one I almost always use.
See the M code below. For more details refer to this blog post from Enterprise DNA.
-
// Code developed by Melissa de Korte
let fnDateTable = ( StartDate as date, EndDate as date, optional FYStartMonthNum as number, optional Holidays as list, optional WDStartNum as number, optional AddRelativeNetWorkdays as logical ) as table =>
let
FYStartMonth = List.Select( {1..12}, each _ = FYStartMonthNum ){0}? ?? 1,
WDStart = List.Select( {0..1}, each _ = WDStartNum ){0}? ?? 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,
ToTable = Table.FromList(AddToday, Splitter.SplitByNothing(), type table [Date = Date.Type] ),
InsertYear = Table.AddColumn(ToTable, "Year", each Date.Year([Date]), type number),
InsertYearOffset = Table.AddColumn(InsertYear, "CurrYearOffset", 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),
InsertQuarterNum = Table.AddColumn(InsertCompletedYear, "Quarter Number", each Date.QuarterOfYear([Date]), type number),
InsertQuarter = Table.AddColumn(InsertQuarterNum, "Quarter", each "Q" & Number.ToText([Quarter Number]), type text),
InsertStartOfQuarter = Table.AddColumn(InsertQuarter, "Start of Quarter", each Date.StartOfQuarter([Date]), type date),
InsertEndOfQuarter = Table.AddColumn(InsertStartOfQuarter, "End of Quarter", each Date.EndOfQuarter([Date]), type date),
InsertCalendarQtr = Table.AddColumn(InsertEndOfQuarter, "Quarter & Year", each "Q" & Number.ToText( Date.QuarterOfYear([Date])) & Date.ToText([Date], [Format = " yyyy"]), type text),
InsertQuarternYear = Table.AddColumn(InsertCalendarQtr, "QuarternYear", each [Year] * 10 + [Quarter Number], type number),
InsertQuarterOffset = Table.AddColumn(InsertQuarternYear, "CurrQuarterOffset", 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, "Month", each Date.Month([Date]), type number),
InsertStartOfMonth = Table.AddColumn(InsertMonth, "Start of Month", each Date.StartOfMonth([Date]), type date),
InsertEndOfMonth = Table.AddColumn(InsertStartOfMonth, "End of Month", each Date.EndOfMonth([Date]), type date),
InsertCalendarMonth = Table.AddColumn(InsertEndOfMonth, "Month & Year", each Text.Proper( Date.ToText([Date], [Format = "MMM yyyy"])), type text),
InsertMonthnYear = Table.AddColumn(InsertCalendarMonth , "MonthnYear", each [Year] * 100 + [Month], type number),
InsertMonthOffset = Table.AddColumn(InsertMonthnYear, "CurrMonthOffset", 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),
InsertMonthName = Table.AddColumn(InsertCompletedMonth, "Month Name", each Text.Proper( Date.ToText([Date], "MMMM")), type text),
InsertMonthShort = Table.AddColumn( InsertMonthName, "Month Short", each Text.Proper( Date.ToText([Date], "MMM")), type text),
InsertMonthInitial = Table.AddColumn(InsertMonthShort, "Month Initial", each Text.Start([Month Name], 1) & Text.Repeat( Character.FromNumber(8203), Date.Month([Date]) ), type text),
InsertDayOfMonth = Table.AddColumn(InsertMonthInitial, "Day of Month", each Date.Day([Date]), type number),
InsertWeekNumber = Table.AddColumn(InsertDayOfMonth, "Week Number", 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)+1<4))
then 1 else Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7), type number),
InsertStartOfWeek = Table.AddColumn(InsertWeekNumber, "Start of Week", each Date.StartOfWeek([Date], Day.Monday), type date),
InsertWeekEnding = Table.AddColumn(InsertStartOfWeek, "End of Week", each Date.EndOfWeek( [Date], Day.Monday), type date),
InsertCalendarWk = Table.AddColumn(InsertWeekEnding, "Week & Year", each "W" & Text.PadStart( Text.From( [Week Number] ), 2, "0") & " " & Text.From(Date.Year( Date.AddDays( Date.StartOfWeek([Date], Day.Monday), 3 ))), type text ),
InsertWeeknYear = Table.AddColumn(InsertCalendarWk, "WeeknYear", each Date.Year( Date.AddDays( Date.StartOfWeek([Date], Day.Monday), 3 )) * 100 + [Week Number], Int64.Type),
InsertWeekOffset = Table.AddColumn(InsertWeeknYear, "CurrWeekOffset", 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) < Date.From(Date.EndOfWeek(CurrentDate, Day.Monday)), type logical),
InsertDayWeek = Table.AddColumn(InsertCompletedWeek, "Day of Week Number", each Date.DayOfWeek([Date], Day.Monday) + WDStart, Int64.Type),
InsertDayName = Table.AddColumn(InsertDayWeek, "Day of Week Name", each Text.Proper( Date.ToText([Date], "dddd" )), type text),
InsertDayInitial = Table.AddColumn(InsertDayName, "Day of Week Initial", each Text.Proper(Text.Start([Day of Week Name], 1)) & Text.Repeat( Character.FromNumber(8203), Date.DayOfWeek([Date], Day.Monday) + WDStart ), type text),
InsertDayOfYear = Table.AddColumn(InsertDayInitial, "Day of Year", each Date.DayOfYear([Date]), Int64.Type),
InsertDayInt = Table.AddColumn(InsertDayOfYear, "DateInt", each [Year] * 10000 + [Month] * 100 + [Day of Month], type number),
InsertDayOffset = Table.AddColumn(InsertDayInt, "CurrDayOffset", each Number.From([Date]) - Number.From(CurrentDate), type number),
InsertIsAfterToday = Table.AddColumn(InsertDayOffset, "IsAfterToday", each not ([Date] <= Date.From(CurrentDate)), type logical),
InsertIsWorkingDay = Table.AddColumn(InsertIsAfterToday, "IsWeekDay", each if Date.DayOfWeek([Date], Day.Monday) > 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 [IsWeekDay] = true and [IsHoliday] <> true then true else false, type logical),
InsertDayType = Table.AddColumn(InsertIsBusinessDay, "Day Type", each if [IsHoliday] = true then "Holiday" else if [IsWeekDay] = false then "Weekend" else if [IsWeekDay] = true then "Weekday" else null, type text),
InsertISOYear = Table.AddColumn( InsertDayType, "ISO Year", each Date.Year( Date.AddDays( Date.StartOfWeek([Date], Day.Monday), 3 )), type number),
InsertISOqNum = Table.AddColumn(InsertISOYear, "ISO Quarter Number", each if [Week Number] >39 then 4 else if [Week Number] >26 then 3 else if [Week Number] >13 then 2 else 1, Int64.Type),
InsertISOqtr = Table.AddColumn(InsertISOqNum, "ISO Quarter", each "Q" & Number.ToText([ISO Quarter Number]), type text),
InsertISOQuarter = Table.AddColumn(InsertISOqtr, "ISO Quarter & Year", each "Q" & Number.ToText([ISO Quarter Number]) & " " & Number.ToText([ISO Year]), type text),
InsertISOqNy = Table.AddColumn(InsertISOQuarter, "ISO QuarternYear", each [ISO Year] * 10 + [ISO Quarter Number], type number),
// BufferTable = Table.Buffer(Table.Distinct( InsertISOqNy[[ISO Year], [DateInt]])),
// 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),
AddFY = Table.AddColumn(InsertISOqNy, "Fiscal Year", each "FY" & (if [Month] >= FYStartMonth and FYStartMonth >1 then Text.From([Year] +1) else Text.From([Year])), type text),
//AddFYs = Table.AddColumn(AddFY, "Fiscal Year short", each "FY" & (if [Month] >= 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 )) & " " & (if [Month] >= FYStartMonth and FYStartMonth >1 then Text.From([Year] +1) else Text.From([Year])), type text),
AddFQnYr = Table.AddColumn(AddFQ, "FQuarternYear", each (if [Month] >= FYStartMonth and FYStartMonth >1 then [Year] +1 else [Year]) * 10 + Number.RoundUp( Date.Month( Date.AddMonths( [Date], - (FYStartMonth -1) )) / 3 ), type number),
AddFM = Table.AddColumn(AddFQnYr, "Fiscal Period Number", each if [Month] >= FYStartMonth and FYStartMonth >1 then [Month] - (FYStartMonth-1) else if [Month] >= FYStartMonth and FYStartMonth =1 then [Month] else [Month] + (12-FYStartMonth+1), type number),
AddFP = Table.AddColumn(AddFM, "Fiscal Period", each "FP" & Text.PadStart( Text.From([Fiscal Period Number]), 2, "0") & " " & (if [Month] >= FYStartMonth and FYStartMonth >1 then Text.From([Year] +1) else Text.From([Year])), type text),
AddFMnYr = Table.AddColumn(AddFP , "FPeriodnYear", each (if [Month] >= FYStartMonth and FYStartMonth >1 then [Year] +1 else [Year]) * 100 + [Fiscal Period Number], type number),
FYCalendarStart = #date( Date.Year(StartDate)-1, FYStartMonth, 1 ),
InsertFFD = Table.AddColumn( AddFMnYr, "FiscalFirstDay", each if [Month] >= FYStartMonth and FYStartMonth >1 then #date( Date.Year([Date])+1, FYStartMonth, 1) else #date( Date.Year([Date]), FYStartMonth, 1), type date ),
InitTable = Table.FromList( List.Transform( {Number.From(FYCalendarStart) .. Number.From(EndDate)}, Date.From), Splitter.SplitByNothing(), type table [DateFW = Date.Type]),
AddFFD = Table.AddColumn( InitTable, "FiscalFirstDay", each if Date.Month([DateFW]) < FYStartMonth then #date(Date.Year([DateFW]), FYStartMonth, 1) else #date(Date.Year([DateFW]) + 1, FYStartMonth, 1)),
AddFWSD = Table.AddColumn( AddFFD, "FWStartDate", each Date.AddYears(Date.StartOfWeek([DateFW], Day.Monday), 1)),
Group1 = Table.Group( AddFWSD, {"FiscalFirstDay", "FWStartDate"}, {{"AllRows", each _, type table [DateFW = nullable date, FiscalFirstDay = date, FWStartDate = date]}}),
Group2 = Table.Group( Group1, {"FiscalFirstDay"}, {{"AllRows2", each _, type table [FiscalFirstDay = date, FWStartDate = date, AllRows = table]}}),
AddIndex = Table.AddColumn( Group2, "Custom", each Table.AddIndexColumn([AllRows2], "Fiscal Week Number", 1, 1) )[[Custom]],
ExpandG2 = Table.ExpandTableColumn( AddIndex, "Custom", {"FiscalFirstDay", "FWStartDate", "AllRows", "Fiscal Week Number"}, {"FiscalFirstDay", "FWStartDate", "AllRows", "Fiscal Week Number"}),
ExpandG1 = Table.ExpandTableColumn( ExpandG2, "AllRows", {"DateFW"}, {"DateFW"} )[[DateFW], [Fiscal Week Number]],
MergeFYW = Table.Join( InsertFFD, {"Date"}, ExpandG1, {"DateFW"}, JoinKind.LeftOuter, JoinAlgorithm.SortMerge ),
FWlogic = List.Contains( {null}, FYStartMonthNum),
UpdateFYWeek = if FWlogic then Table.ReplaceValue(MergeFYW, each [Fiscal Week Number], each if FYStartMonth =1 then [Week Number] else [Fiscal Week Number], Replacer.ReplaceValue, {"Fiscal Week Number"}) else MergeFYW,
AddFYW = Table.AddColumn( UpdateFYWeek, "Fiscal Week", each if FWlogic then "F" & [#"Week & Year"] else if FYStartMonth =1 then "FW" & Text.PadStart( Text.From([Fiscal Week Number]), 2, "0") & Date.ToText([Date], " yyyy") else if Date.Month([Date]) < FYStartMonth then "FW" & Text.PadStart( Text.From([Fiscal Week Number]), 2, "0") & Date.ToText([Date], " yyyy") else "FW" & Text.PadStart(Text.From([Fiscal Week Number]), 2, "0") & " " & Text.From( Date.Year([Date])+1), type text),
InsertFWeeknYear = Table.AddColumn(AddFYW, "FWeeknYear", each if FWlogic then [WeeknYear] else (if FYStartMonth =1 then Date.Year([Date]) else if Date.Month([Date]) < FYStartMonth then Date.Year([Date]) else Date.Year([Date])+1) * 100 + [Fiscal Week Number], Int64.Type),
CurrentDateRecord = Table.SelectRows(InsertFWeeknYear, each ([Date] = CurrentDate)),
CurrentISOyear = CurrentDateRecord{0}[ISO Year],
CurrentISOqtr = CurrentDateRecord{0}[ISO Quarter Number],
CurrentYear = CurrentDateRecord{0}[Year],
CurrentMonth = CurrentDateRecord{0}[Month],
CurrentFiscalFirstDay = CurrentDateRecord{0}[FiscalFirstDay],
PrevFiscalFirstDay = Date.AddYears(CurrentFiscalFirstDay, -1),
CurrentFY = CurrentDateRecord{0}[Fiscal Year],
CurrentFQ = CurrentDateRecord{0}[FQuarternYear],
CurrentFP = CurrentDateRecord{0}[FPeriodnYear],
CurrentFW = CurrentDateRecord{0}[FWeeknYear],
InsertISOYrOffset = Table.AddColumn(InsertFWeeknYear, "ISO CurrYearOffset", each [ISO Year] - CurrentISOyear, type number),
InsertISOQtrOffset = Table.AddColumn(InsertISOYrOffset, "ISO CurrQuarterOffset", each ((4 * [ISO Year]) + [ISO Quarter Number]) - ((4 * CurrentISOyear) + CurrentISOqtr), type number),
InsertFYoffset = Table.AddColumn(InsertISOQtrOffset, "Fiscal CurrYearOffset", each try (if [Month] >= FYStartMonth then [Year]+1 else [Year]) - (if CurrentMonth >= FYStartMonth then CurrentYear+1 else CurrentYear) otherwise null, type number),
InsertCurrentFY = Table.AddColumn(InsertFYoffset, "IsCurrentFY", each if [Fiscal Year] = CurrentFY then true else false, type logical),
InsertCurrentFQ = Table.AddColumn(InsertCurrentFY, "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] = InsertISOYrOffset 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 [Fiscal CurrYearOffset] = -1 and List.Contains(ListPrevFYDates, [Date] ) then true else false, type logical),
InsertNetWorkdays = if AddRelativeNetWorkdays = true then Table.AddColumn(InsertPFYTD, "Relative Networkdays", each fxNETWORKDAYS( StartDate, [Date], Holidays ), type number ) else InsertPFYTD,
fxNETWORKDAYS = (StartDate, EndDate, optional Holidays as list) =>
let
ListOfDates = List.Dates( StartDate, Number.From(EndDate-StartDate)+1, Duration.From(1) ),
DeleteHolidays = if Holidays = null then ListOfDates else List.Difference( ListOfDates, List.Transform(Holidays, Date.From )),
DeleteWeekends = List.Select( DeleteHolidays, each Date.DayOfWeek( _, Day.Monday) < 5 ),
CountDays = List.Count( DeleteWeekends)
in
CountDays,
RemoveToday = Table.RemoveColumns( if EndDate < CurrentDate then Table.SelectRows(InsertNetWorkdays, each ([Date] <> CurrentDate)) else InsertNetWorkdays, {"Day of Year", "FiscalFirstDay"}),
ChType = Table.TransformColumnTypes(RemoveToday,{{"Year", Int64.Type}, {"Quarter Number", Int64.Type}, {"Month", Int64.Type}, {"Day of Month", Int64.Type}, {"DateInt", Int64.Type}, {"Day of Week Number", Int64.Type}, {"ISO CurrYearOffset", Int64.Type}, {"ISO QuarternYear", Int64.Type}, {"ISO CurrQuarterOffset", Int64.Type}, {"Week Number", Int64.Type}, {"WeeknYear", Int64.Type}, {"MonthnYear", Int64.Type}, {"QuarternYear", Int64.Type}, {"FQuarternYear", Int64.Type}, {"Fiscal Period Number", Int64.Type}, {"FPeriodnYear", Int64.Type}, {"CurrWeekOffset", Int64.Type}, {"CurrMonthOffset", Int64.Type}, {"CurrQuarterOffset", Int64.Type}, {"CurrYearOffset", Int64.Type}, {"Fiscal CurrYearOffset", Int64.Type}, {"Fiscal Week Number", Int64.Type}}),
ReorderCols = Table.ReorderColumns(ChType,{"Date", "Year", "CurrYearOffset", "YearCompleted", "Quarter Number", "Quarter", "Start of Quarter", "End of Quarter", "Quarter & Year", "QuarternYear", "CurrQuarterOffset", "QuarterCompleted", "Month", "Start of Month", "End of Month", "Month & Year", "MonthnYear", "CurrMonthOffset", "MonthCompleted", "Month Name", "Month Short", "Month Initial", "Day of Month", "Week Number", "Start of Week", "End of Week", "Week & Year", "WeeknYear", "CurrWeekOffset", "WeekCompleted", "Day of Week Number", "Day of Week Name", "Day of Week Initial", "DateInt", "CurrDayOffset", "IsAfterToday", "IsWeekDay", "IsHoliday", "IsBusinessDay", "Day Type", "ISO Year", "ISO CurrYearOffset", "ISO Quarter Number", "ISO Quarter", "ISO Quarter & Year", "ISO QuarternYear", "ISO CurrQuarterOffset", "Fiscal Year", "Fiscal CurrYearOffset", "Fiscal Quarter", "FQuarternYear", "Fiscal Period Number", "Fiscal Period", "FPeriodnYear", "DateFW", "Fiscal Week Number", "Fiscal Week", "FWeeknYear", "IsCurrentFY", "IsCurrentFQ", "IsCurrentFP", "IsCurrentFW", "IsPYTD", "IsPFYTD"}),
ListCols = if FWlogic then Table.RemoveColumns(ReorderCols,{"ISO Quarter Number", "Fiscal Year", "Fiscal Quarter", "FQuarternYear", "Fiscal Period Number", "Fiscal Period", "FPeriodnYear", "DateFW", "Fiscal Week Number", "Fiscal Week", "FWeeknYear", "Fiscal CurrYearOffset", "IsCurrentFQ", "IsCurrentFP", "IsCurrentFW"}) else Table.RemoveColumns(ReorderCols,{"Fiscal Period Number", "DateFW", "Fiscal Week Number", "ISO Quarter Number"})
in
ListCols,
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 = " 2.01: full code review",
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)
(AddRelativeNetWorkdays) if true adds a Relative Networkdays column to the date table #(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)
[IsWeekDay] 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 #(lf)
IMPORTANT! No Fiscal columns will be added if the (FYStartMonthNum) is omitted",
Result = " " ] }
]
in
Value.ReplaceType( fnDateTable, Value.ReplaceMetadata( Value.Type( fnDateTable ), Documentation ))
Option 2: Use external tool Bravo to create the date table
Another option is to create a date table using the external tool Bravo developed by the SQLBI team. I think this a good option if you want to do a quick analysis. However, I still prefer the date table from the M code above
Tip 4: Update Power BI metadata simultaneously
Imagine the following scenarios as an example:
You have your date table and also other date columns in your data model. You want to change the data type format to “short date” automatically
You want to set the “summarize by” to “none” for all columns in your data model
The two examples above can be easily performed using an external tool called Metadata Mechanic Pro developed by Greg Deckler. See below the screenshot from Metadata Mechanic Pro, where I set all date types to “short date” and aggregation to “none” for all columns:
Tip 5: Check referential integrity violation in your data model
One thing that I always do during data model development is to check for referential integrity violation. Referential integrity violation means that foreign key values in a fact table are missing from the dimension table. One example would be having foreign keys as null values in the fact table causing a referential integrity violation. For more details refer to this article from Kimball Group.
Option 1: Use the external tool DAX Studio
This option is useful when you only want to check during development and you don’t need to dynamically check it for every semantic model refresh (if you want to dynamically check, see option 2).
How to check it using DAX Studio:
I already wrote a LinkedIn post describing how to check for referential integrity violation. Please check this post here.
Option 2: Dynamically check for referential integrity
Another option is to leverage DAX measures to check for referential integrity violation. Here credit to Bernat Agulló Roselló. He wrote about it in his blog. So, there is no point of repeating here again. Check his blog post here where he describes in great detail how to do it.
Tip 6: Data model documentation
Model documentation is a topic that might be forgotten or left behind. But this is an important topic, especially if you want to share your semantic models with other people. One option to document your data model is to use the external tool Model Documenter developed by Marc Lelijveld.
This external tool generates a Power BI report with a lot of information:
Tip 7: Parameterizing source connections
There are many use cases for using parameters in Power Query. One of them is to create a parameter to change the data source dynamically. For example, you work with SQL Server as data source and often need to switch between environments (such as moving between development and production). Doing that you don’t need to change the data source connection for every table manually, because you can use the parameter to do it all at once.
I already wrote a LinkedIn post describing how to create a parameter for your data source in Power Query. Please check this post here.
Tip 8: Best Practice Analyzer (BPA) to improve your model’s performance
Before publishing your data model, it is a good idea to check if there are any issues within the model. This check can help us, especially when working with large data models.
To run this check, you have to first run a C# script in Tabular Editor. There are already many resources out there explaining how to do it. Therefore, there is no point repeating it here.
See some resources below:
Blog post from Data Mozart: https://data-mozart.com/mastering-dp-500-exam-optimize-data-model-with-tabular-editor/
Video from Guy in a Cube: https://www.youtube.com/watch?v=1Qan0_VmZRw
Article from Microsoft: https://powerbi.microsoft.com/en-us/blog/best-practice-rules-to-improve-your-models-performance/
Tip 9: Quickly check DAX measure dependencies
For this one, you need to have at least the November 2023 version of Power BI Desktop. With this version, Microsoft released a new feature called DAX Query View inside of Power BI Desktop. I personally really like this feature, because we can perform some things that were only possible using DAX Studio, such as performing a DAX query without leaving Power BI Desktop.
When working with large models with a lot of DAX measures, it can become quite hard to find DAX measure dependency. Well, right now we can perform this task easily in Power BI Desktop without using any external tool :)
See below the screenshot with an example:
For more details about this new feature, I highly recommend to watch the YouTube video from Marco Russo here where he really dive into this feature comparing with DAX Studio as well.
Another great resource is the article from Zoe Douglas from Microsoft here.
Conclusion
In this post we talked about some ideas and techniques to automate manual tasks and to optimize your data model development in Power BI. Of course, there are definitely many other ways to automate such tasks. The purpose of this post is to show you some possibilities.
Do you know any other technique or idea to make our life easier during data model development? Please leave a comment below or on my LinkedIn post here.
Thanks for reading!
Last updated on December 13, 2023