Power BI Dataflow Best Practice
If you are about to start your journey with Power BI Dataflow, I suggest developing a standard calendar or date table that everyone within the organization can use.
Power BI Dataflows are all about reusing clean and pre-approved data, so having a company-wide approved calendar table to use in reporting is one of the best practices to implement for Dataflows. It is especially true when an organisation follows a different financial calendar or a special, like 4-5-4 week calendar.
A more senior developer can create the dates and all the other columns to use in reporting. Not only that but Dataflows can be promoted and certified, making it much easier to identify what to use.*
*at the time of writing this post, endorsement options are NOT available for Dataflows, but it seems to be a bug within the UI.
Additionally, this step will ensure consistency across all reports and guarantees that all time intelligence calculations will work fine.
And if you are here for the M code, you can find it below:
Calendar Query:
Source = List.Dates(CalendarStart, NoOfDays, #duration(1, 0, 0, 0)),
#”Converted to Table” = Table.FromList(
Source,
Splitter.SplitByNothing(),
null,
null,
ExtraValues.Error
),
#”Renamed Columns” = Table.RenameColumns(#”Converted to Table”, {{“Column1”, “Date”}}),
#”Changed Type” = Table.TransformColumnTypes(#”Renamed Columns”, {{“Date”, type date}}),
#”Inserted Year” = Table.AddColumn(#”Changed Type”, “Year”, each Date.Year([Date]), Int64.Type),
#”Inserted start of year” = Table.AddColumn(
#”Inserted Year”,
“Start of year”,
each Date.StartOfYear([Date]),
type nullable date
),
#”Inserted end of year” = Table.AddColumn(
#”Inserted start of year”,
“End of year”,
each Date.EndOfYear([Start of year]),
type nullable date
),
#”Inserted quarter” = Table.AddColumn(
#”Inserted end of year”,
“Quarter”,
each Date.QuarterOfYear([Date]),
type nullable number
),
#”Inserted Merged Column” = Table.AddColumn(
#”Inserted quarter”,
“Qtr”,
each Text.Combine({“Q”, Text.From([Quarter], “en-US”)}),
type text
),
#”Inserted month” = Table.AddColumn(
#”Inserted Merged Column”,
“Month”,
each Date.Month([Date]),
type nullable number
),
#”Inserted month name” = Table.AddColumn(
#”Inserted month”,
“Month name”,
each Date.MonthName([Date]),
type nullable text
),
#”Inserted days in month” = Table.AddColumn(
#”Inserted month name”,
“Days in month”,
each Date.DaysInMonth([Date]),
type nullable number
),
#”Inserted week of year” = Table.AddColumn(
#”Inserted days in month”,
“Week of year”,
each Date.WeekOfYear([Date]),
type nullable number
),
#”Inserted start of week” = Table.AddColumn(
#”Inserted week of year”,
“Start of week”,
each Date.StartOfWeek([Date]),
type nullable date
),
#”Inserted day name” = Table.AddColumn(
#”Inserted start of week”,
“Day name”,
each Date.DayOfWeekName([Date]),
type nullable text
),
#”Inserted day of year” = Table.AddColumn(
#”Inserted day name”,
“Day of year”,
each Date.DayOfYear([Date]),
type nullable number
),
#”Inserted day of week” = Table.AddColumn(
#”Inserted day of year”,
“Day of week”,
each Date.DayOfWeek(([Date]), Day.Monday) + 1,
type nullable number
)
in
#”Inserted day of week”
Financial Calendar Query:
Source = List.Dates(CalendarStart, NoOfDays, #duration(1, 0, 0, 0)),
#”Converted to Table” = Table.FromList(
Source,
Splitter.SplitByNothing(),
null,
null,
ExtraValues.Error
),
#”Renamed Columns” = Table.RenameColumns(#”Converted to Table”, {{“Column1”, “Date”}}),
#”Changed Type” = Table.TransformColumnTypes(#”Renamed Columns”, {{“Date”, type date}}),
#”Added custom 1″ = Table.AddColumn(
#”Changed Type”,
“Fin_Date”,
each Date.AddMonths([Date], 13 – FinYearStart),
type date
),
#”Inserted year” = Table.AddColumn(
#”Added custom 1″,
“Fin_Year”,
each Date.Year([Fin_Date]),
type number
),
#”Inserted quarter” = Table.AddColumn(
#”Inserted year”,
“Fin_Quarter”,
each Date.QuarterOfYear([Fin_Date]),
type number
),
#”Inserted Merged Column” = Table.AddColumn(
#”Inserted quarter”,
“Fin_Qtr”,
each Text.Combine({“Q”, Text.From([Fin_Quarter], “en-US”)}),
type text
),
#”Inserted month” = Table.AddColumn(
#”Inserted Merged Column”,
“Month”,
each Date.Month([Fin_Date]),
type number
),
#”Inserted month name” = Table.AddColumn(
#”Inserted month”,
“Month name”,
each Date.MonthName([Date]),
type nullable text
),
#”Inserted days in month” = Table.AddColumn(
#”Inserted month name”,
“Days in Fin_month”,
each Date.DaysInMonth([Fin_Date]),
type number
),
#”Renamed columns 1″ = Table.RenameColumns(#”Inserted days in month”, {{“Month”, “Fin_Month”}})
in
#”Renamed columns 1″
Roland
🙏Thanks for watching and if you liked this video please hit the 👍 button.
📢 Also be sure to subscribe to the channel so you never miss a new video.
0 Comments