Date issue

[jwi1]

Good day,

I hope someone can help me with the following.

I have the below data.

As you can see in 2020, in week 7, the employee worked 8 hours on monday, 8 hours on tuesday and so on.

Unfortunately, there are only days mentioned (MO, TU etc), not dates (10/2/2020 - 16/02/2020).

I am looking for a solution to translate the days into dates.

The info is coming from 2 tables, as mentioned below.

Thanks!

John

This is how it looks now:

YEAR	WEEK	MO	TU	WE	TH	FR	SA	SU
2020	7	8	8	8	8	8	0	0

And this is what I am looking for:

YEAR	WEEK	10/2/2020	11/02/2020	12/2/2020	13/2/2020	14/2/2020	15/2/2020	16/2/2020
2020	7	8	8	8	8	8	0	0

this are the 2 tables:

HRM_TIMECARD:

HR_TIMECARDLINE

[AllisonKennedy · Accepted solution]

You can expand this report to work for the entire year by adding a DimDate table to your model and creating a Date Key column that can get the date for you based on Year, Week Number and Day Name.

Here is the M code for the sample data I created for your scenario, just paste it into a New Query Blank in the Power Query Editor:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAWMLNGwGxCAUqwNRY0RAjRER5hCjxpgINSZEqDElpCYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TIMECARD_ID = _t, PROJECT_ID = _t, MO = _t, TU = _t, WE = _t, TH = _t, FR = _t, SA = _t, SU = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"TIMECARD_ID", Int64.Type}, {"PROJECT_ID", Int64.Type}, {"MO", Int64.Type}, {"TU", Int64.Type}, {"WE", Int64.Type}, {"TH", Int64.Type}, {"FR", Int64.Type}, {"SA", Int64.Type}, {"SU", Int64.Type}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"TIMECARD_ID"}, HRM_TIMECARD, {"ID"}, "HRM_TIMECARD", JoinKind.LeftOuter),
#"Expanded HRM_TIMECARD" = Table.ExpandTableColumn(#"Merged Queries", "HRM_TIMECARD", {"Year", "Week"}, {"Year", "Week"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded HRM_TIMECARD",{{"MO", "Monday"}, {"TU", "Tuesday"}, {"WE", "Wednesday"}, {"TH", "Thursday"}, {"FR", "Friday"}, {"SA", "Saturday"}, {"SU", "Sunday"}}),
#"Unpivoted Only Selected Columns" = Table.Unpivot(#"Renamed Columns", {"Sunday", "Saturday", "Friday", "Thursday", "Wednesday", "Tuesday", "Monday"}, "Day Name", "Hours"),
#"Inserted Merged Column" = Table.AddColumn(#"Unpivoted Only Selected Columns", "Date Key", each Text.Combine({Text.From([Year]), Text.From([Week], "en-US"), [Day Name]}, ""), type text)
in
#"Inserted Merged Column"

You'll see I've done an unpivot for the days of the week and then combined that with the week and year data from your other table.

Do the same Merge column in your DimDate table. See a post here for help on creating DimDate; https://allisonkennedycv.blogspot.com/2020/04/dimdate-what-why-and-how.html

Or the updated M code for your scenario:

let
startDate = #date(2020, 1, 1),
endDate = Date.From(DateTime.LocalNow()),
Dates = List.Dates(startDate, Duration.Days(endDate - startDate), #duration (1,0,0,0)),
#"Converted to Table" = Table.FromList(Dates, 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 Quarter" = Table.AddColumn(#"Inserted Year", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
#"Added FY Quarters" = Table.AddColumn(#"Inserted Quarter", "FY Quarter", each if [Quarter] = 1 then "Q4" else if [Quarter] = 2 then "Q1" else if [Quarter] = 3 then "Q2" else "Q3", type text),
#"Inserted Month Name" = Table.AddColumn(#"Added FY Quarters", "Month name", each Date.MonthName([Date]), type text),
#"Inserted Month" = Table.AddColumn(#"Inserted Month Name", "Month number", each Date.Month([Date]), Int64.Type),
#"Inserted Week of Year" = Table.AddColumn(#"Inserted Month", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type),
#"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year", "Week of Month", each Date.WeekOfMonth([Date]), Int64.Type),
#"Inserted Day of Year" = Table.AddColumn(#"Inserted Week of Month", "Day of Year", each Date.DayOfYear([Date]), Int64.Type),
#"Inserted Day of Week" = Table.AddColumn(#"Inserted Day of Year", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type),
#"Inserted Day of Month" = Table.AddColumn(#"Inserted Day of Week", "Day of month", each Date.Day([Date]), Int64.Type),
#"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Month", "Day name", each Date.DayOfWeekName([Date]), type text),
#"Inserted Merged Column" = Table.AddColumn(#"Inserted Day Name", "Date Key", each Text.Combine({Text.From([Year], "en-US"), Text.From([Week of Year], "en-US"), [Day name]}, ""), type text)
in
#"Inserted Merged Column"

Close and Apply the changes and create a relationship between Date Key in DimDate and HR_TIMECARDLINE tables.

This should get you what you need for any calculation or desired output.

[jwi1]

Hi Allison,

This is what I was looking for!

Thanks!!!

John