The ability to use Dataflows to collate your data and spread the load on your production or report servers is a huge benefit, it is so simple to set up and can achieve so much more than was previously possible for many business based content creators. In this video, we went through how to quickly and easily combine tables of data from multiple dataflows into a single table. While I expect your real-world scenarios will be simpler, this is a really great way to see how easy it is to combine data in Power BI.
Calendar Table (in M)
let
//ADD YOUR START Year
Year = 2020,
//WHOLE NUMBER OF YEARS FOR CALENDAR
Length = 5,
//ONLY EDIT BELOW HERE IF YOU KNOW WHAT YOU’RE DOING
StartDate = #date(Year, 01, 01),
EndDate = Date.AddYears(StartDate, Length),
Duration = Duration.Days ( EndDate – StartDate ),
DateColumn = List.Dates(StartDate, Duration+1, #duration(1,0,0,0)),
#”Converted to Table” = Table.FromList(DateColumn, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#”Renamed Columns” = Table.RenameColumns(#”Converted to Table”,{{“Column1”, “Dates”}}),
#”Changed Type” = Table.TransformColumnTypes(#”Renamed Columns”,{{“Dates”, type date}}),
#”Inserted Month” = Table.AddColumn(#”Changed Type”, “Month”, each Date.Month([Dates]), Int64.Type),
#”Inserted Month Name” = Table.AddColumn(#”Inserted Month”, “Month Name”, each Date.MonthName([Dates]), type text),
#”Inserted Year” = Table.AddColumn(#”Inserted Month Name”, “Year”, each Date.Year([Dates]), Int64.Type),
#”Inserted Day” = Table.AddColumn(#”Inserted Year”, “Day”, each Date.Day([Dates]), Int64.Type),
#”Inserted Day of Week” = Table.AddColumn(#”Inserted Day”, “Day of Week”, each Date.DayOfWeek([Dates]), Int64.Type),
#”Inserted Day Name” = Table.AddColumn(#”Inserted Day of Week”, “Day Name”, each Date.DayOfWeekName([Dates]), type text)
in
#”Inserted Day Name”
Alternative EndDate Line
EndDate = Date.EndOfYear(DateTime.Date(DateTime.LocalNow())),