M-code with Date table structure

Power Query
1

Hi all,

I would like to change the structure of the availability. I have the startdate, einddate, employee ID and hours per day of the week odd / even in 1 row. I would like to get this in 1 column with the availability per date. See the attached pbix file and Excel file.

Result: I would like to know which employee was available of which day for how many hours.

Is this possible with the Query Editor or M-code ? Or is there a different way to get the same result?

If something is unclear, please ask me!

Nick van Dijk
Availablity v1.pbix (344.9 KB)
Example availability hours.xlsx (18.8 KB)

2

Hi Nick and welcome to the EDNA forum community.

Please take a look at this video from Curbal. Ruth is excellent at explaining how to get each date that falls between a ā€˜start and endā€™ date. I think once you get those dates in your table, you will be able to get the hours calculated.

Here is the video link:
List dates between two dates

Let me know if this was helpful.
All the best.
Linda

3 Likes
3

Hi @Nick1,

Welcome to the Forum!

Beside the reference provided by @lsquintana17, you could also explore something like this.
Add an Odd-/Even week field to your date table with something like this:

Table.AddColumn( PrevStepNameHere, "Odd-/Even Week", each 
    if Number.IsOdd([ISO Weeknumber]) 
    then "Odd" 
    else "Even"
)

Create a supporting query by referencing your Date table and extracting one these fields
Load this table into memory with Table.Buffer - Iā€™ve called this query DateSmall

image

Merge that with an unpivoted version of your Contract table. You can copy and paste this example into a new blank query. After youā€™ve added the DateSmall table described above.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQAAhMLJR0lQ10zXSMDI0M409QAxDQAkRZYsAEU45MzUIrVAdqBMB0kaGyoawq3iVLjYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, startdate = _t, enddate = _t, employee_id = _t, hours_monday_odd = _t, hours_tuesday_odd = _t, hours_wednesday_odd = _t, hours_thursday_odd = _t, hours_friday_odd = _t, hours_saturday_odd = _t, hours_sunday_odd = _t, hours_monday_even = _t, hours_tuesday_even = _t, hours_wednesday_even = _t, hours_thursday_even = _t, hours_friday_even = _t, hours_saturday_even = _t, hours_sunday_even = _t, internal_price_per_hour = _t]),
    ChType = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"startdate", type date}, {"enddate", type date}, {"employee_id", Int64.Type}, {"hours_monday_odd", Int64.Type}, {"hours_tuesday_odd", Int64.Type}, {"hours_wednesday_odd", Int64.Type}, {"hours_thursday_odd", Int64.Type}, {"hours_friday_odd", Int64.Type}, {"hours_saturday_odd", Int64.Type}, {"hours_sunday_odd", Int64.Type}, {"hours_monday_even", Int64.Type}, {"hours_tuesday_even", Int64.Type}, {"hours_wednesday_even", Int64.Type}, {"hours_thursday_even", Int64.Type}, {"hours_friday_even", Int64.Type}, {"hours_saturday_even", Int64.Type}, {"hours_sunday_even", Int64.Type}, {"internal_price_per_hour", Int64.Type}}),
    UnpivotOthers = Table.UnpivotOtherColumns(ChType, {"id", "startdate", "enddate", "employee_id", "internal_price_per_hour"}, "Attribute", "Value"),
    ReplaceValue = Table.ReplaceValue(UnpivotOthers, each [Attribute], each Text.Proper( Text.AfterDelimiter( [Attribute], "_")),Replacer.ReplaceValue,{"Attribute"}),
    SplitByDelimiter = Table.SplitColumn(ReplaceValue, "Attribute", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, false), {"Weekday name", "odd-/Even Week"}),
    MergeDateSmall = Table.NestedJoin(SplitByDelimiter, {"Weekday name", "odd-/Even Week"}, DateSmall, {"DayOfWeekName", "Odd-/Even Week"}, "DateSmall", JoinKind.LeftOuter),
    FilterDateSmall = Table.ReplaceValue(MergeDateSmall, each [DateSmall], each Table.SelectRows([DateSmall], (x)=> x[Date] >= [startdate] and x[Date] <= [enddate]),Replacer.ReplaceValue,{"DateSmall"}),
    ExpandDate = Table.ExpandTableColumn(FilterDateSmall, "DateSmall", {"Date"}, {"Date"}),
    RemoveColumns = Table.RemoveColumns(ExpandDate,{"Weekday name", "odd-/Even Week", "startdate", "enddate", "internal_price_per_hour"})
in
    RemoveColumns

With this result.

image
image689Ɨ629 9.29 KB

Hereā€™s your sample file (please note to include a data file in future along with your PBIX.)
Availablity v1.pbix (350.2 KB)

I hope this is helpful

2 Likes
4

Hi @Melissa Thank you so much. This is exactly what I am looking for. I have some additional questions:

  • Iā€™ve added Odd and Even to the date table. Check.
  • In your example it only gives the result of one employee, what do I need to do to get the result of all the employees? (column employee_id)
  • My environment is all in English, but my DayOfWeekName is in Dutch. How can I change this to English, because the query doesnā€™t work properly because the result of date is null.

image

  • Iā€™ve added the data file (json) that belong to the pbix file
    contract3.json (107.0 KB)

Thank you!
(Dank je wel)

Nick

5

Hi @Nick1,

  1. :+1: Great work
  2. Letā€™s do this one lastā€¦
  3. Add a culture to the DayOfWeek name: Date.ToText([Date], ā€œddddā€, ā€œen-USā€) Iā€™ve incorporated this in the date table M function within your file

(Revisit 2) Once the above is in place you can pretty much copy the logic from the Sample query already provided OR re-create the steps listed below (that will require writing some M code :wink:).

  • Unpivot other columns
  • Replace values in the Attribute column
  • Split it by delimiter
  • Merge with the DateSmall query
  • Filter the nested table to the required range
  • Expand the nested table
  • Remove columns

Thanks for providing the sample data! Iā€™ve added a FileLocation parameter, just select your location from that list and the query will restore. Hereā€™s the code for reference.

let
    Source = Contract,
    UnpivotOthers = Table.UnpivotOtherColumns(Source, {"id", "startdate", "enddate", "employee_id", "internal_price_per_hour"}, "Attribute", "Value"),
    ReplaceValue = Table.ReplaceValue(UnpivotOthers, each [Attribute], each Text.Proper( Text.AfterDelimiter( [Attribute], "_")),Replacer.ReplaceValue,{"Attribute"}),
    SplitByDelimiter = Table.SplitColumn(ReplaceValue, "Attribute", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, false), {"Weekday name", "odd-/Even Week"}),
    MergeDateSmall = Table.NestedJoin(SplitByDelimiter, {"Weekday name", "odd-/Even Week"}, DateSmall, {"DayOfWeekName", "Odd-/Even Week"}, "DateSmall", JoinKind.LeftOuter),
    FilterDateSmall = Table.ReplaceValue(MergeDateSmall, each [DateSmall], each Table.SelectRows([DateSmall], (x)=> x[Date] >= [startdate] and x[Date] <= [enddate]),Replacer.ReplaceValue,{"DateSmall"}),
    ExpandDate = Table.ExpandTableColumn(FilterDateSmall, "DateSmall", {"Date"}, {"Date"}),
    RemoveColumns = Table.RemoveColumns(ExpandDate,{"Weekday name", "odd-/Even Week", "startdate", "enddate", "internal_price_per_hour"})
in
    RemoveColumns

Your PBIX file:
Availablity v1.pbix (356.1 KB)

I hope this is helpful
(graag gedaan!)

2 Likes
6

Awesome advice. Thank you a lot!

1 Like