M language custom column between dates/timestamps

[Anonymous]

Dear all,

I have 2 relevant columns in my excel source file:

- from (time datatype)

- to (time datatype)

In M language, i would like to add 12 columns

- 8h-9h

- 9h-10h

- 10h-11h

- ....

- 17h-18h

- 18h-19h

- 19h-20h

(At 20h, all shops are closed (and earlier) )

The fields always have a value from 0.00 to 1.00

When the person has entered 'vanaf' (=from) 09h35,

The field 8h-9h should be 0 (zero),

the field 9h-10h should be 0,42 (25/60)

the field 10h-11h should be 1

etcetera.

I need it in M language because I want to pivot the fields to rows.

I started with a custom column but it's imposible to use a 'between' funcion

Many thanks for your feedback,

[Anonymous · Accepted solution]

Solved (by myself)

let
    Source = Excel.Workbook(File.Contents("C:\Users\lp\Documents\POC_BI\CompanyName_27062019.xlsx"), null, true),
    CompanyName_201906271531_Table = Source{[Item="CompanyName_201906271531",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(CompanyName_201906271531_Table,{{"DOTSID", Int64.Type}, {"EVOID", Int64.Type}, {"FRM", Int64.Type}, {"AB", type text}, {"WKN", Int64.Type}, {"ORGANISATIE", type text}, {"NAAM", type text}, {"VOORNAAM", type text}, {"GEBOORTEDATUM", type date}, {"TAAL", type text}, {"KOSTENPLAATSCODE", type text}, {"KOSTENPLAATSNAAM", type text}, {"PROFIELTYPE", type text}, {"DATUM", type date}, {"VANAF", type datetime}, {"TOT", type datetime}, {"DUUR", Int64.Type}, {"TOTALEDUUR", type datetime}, {"TYPENAAM", type text}, {"KOSTENPLAATSCODEPLANNING", type text}, {"KOSTENPLAATSNAAMPLANNING", type text}}),
    #"Removed NAME Columns" = Table.RemoveColumns(#"Changed Type",{"NAAM", "VOORNAAM"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed NAME Columns",{{"VANAF", type time}, {"TOT", type time}, {"TOTALEDUUR", type time}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "PlanningActuals", each if [DATUM] > Date.AddDays(Date.From(DateTime.FixedLocalNow()),-1)  then "Planning" else "Actuals"),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"KOSTENPLAATSNAAM"}, DimShop, {"ShopName"}, "DimShop", JoinKind.LeftOuter),
    #"Expanded DimShop" = Table.ExpandTableColumn(#"Merged Queries", "DimShop", {"ShopCodeShort"}, {"DimShop.ShopCodeShort"}),
    #"Added Custom2" = Table.AddColumn(#"Expanded DimShop", "CustomKEY", each 963),
    #"7h00" = Table.AddColumn(#"Added Custom2", "07:00", each if [VANAF] >= Time.FromText("7:29:00") 
and [TOT] <= Time.FromText("7:30:00") 
then (if (Duration.TotalMinutes( Time.FromText("10:00:00") - [VANAF] )) >= 30 then 30 else Duration.TotalMinutes( Time.FromText("10:00:00") - [VANAF] ))/30
else 0),
#"7h30" = Table.AddColumn(#"7h00", "07:30", each if [VANAF] >= Time.FromText("7:59:00") 
and [TOT] <= Time.FromText("8:00:00") 
then (if (Duration.TotalMinutes( Time.FromText("8:00:00") - [VANAF] )) >= 30 then 30 else Duration.TotalMinutes( Time.FromText("10:00:00") - [VANAF] ))/30
else 0),
#"8h00" = Table.AddColumn(#"7h30", "08:00", each if [VANAF] >= Time.FromText("8:29:00") 
and [TOT] <= Time.FromText("7:30:00") 
then (if (Duration.TotalMinutes( Time.FromText("10:00:00") - [VANAF] )) >= 30 then 30 else Duration.TotalMinutes( Time.FromText("10:00:00") - [VANAF] ))/30
else 0),
#"8h30" = Table.AddColumn(#"8h00", "08:30", each if [VANAF] <= Time.FromText("8:59:00") 
and [TOT] <= Time.FromText("9:00:00") 
then (if (Duration.TotalMinutes( Time.FromText("9:00:00") - [VANAF] )) >= 30 then 30 else Duration.TotalMinutes( Time.FromText("9:00:00") - [VANAF] ))/30
else 0),
#"9h00" = Table.AddColumn(#"8h30", "09:00", each if [VANAF] <= Time.FromText("9:29:00") 
and [TOT] >= Time.FromText("9:01:00") 
then (if Duration.TotalMinutes( Time.FromText("9:30:00") - [TOT] ) < 30 and Duration.TotalMinutes( Time.FromText("9:30:00") - [TOT] ) > 1 then Duration.TotalMinutes( [TOT] - Time.FromText("9:00:00") ) else 
if (Duration.TotalMinutes( Time.FromText("9:30:00") - [VANAF] )) >= 30 then 30 else Duration.TotalMinutes( Time.FromText("9:30:00") - [VANAF] ))/30
else 0),
    #"9h30" = Table.AddColumn(#"9h00", "09:30", each if [VANAF] <= Time.FromText("9:59:00") 
and [TOT] >= Time.FromText("9:31:00") 
then (if Duration.TotalMinutes( Time.FromText("10:00:00") - [TOT] ) < 30 and Duration.TotalMinutes( Time.FromText("10:00:00") - [TOT] ) > 1 then Duration.TotalMinutes( [TOT] - Time.FromText("9:30:00") ) else 
if (Duration.TotalMinutes( Time.FromText("10:00:00") - [VANAF] )) >= 30 then 30 else Duration.TotalMinutes( Time.FromText("10:00:00") - [VANAF] ))/30
else 0),
    #"10h00" = Table.AddColumn(#"9h30", "10:00", each if [VANAF] <= Time.FromText("10:29:00") 
and [TOT] >= Time.FromText("10:01:00") 
then (if Duration.TotalMinutes( Time.FromText("10:30:00") - [TOT] ) < 30 and Duration.TotalMinutes( Time.FromText("10:30:00") - [TOT] ) > 1 then Duration.TotalMinutes( [TOT] - Time.FromText("10:00:00") ) else 
if (Duration.TotalMinutes( Time.FromText("10:30:00") - [VANAF] )) >= 30 then 30 else Duration.TotalMinutes( Time.FromText("10:30:00") - [VANAF] ))/30
else 0),
    #"10h30" = Table.AddColumn(#"10h00", "10:30", each if [VANAF] <= Time.FromText("10:59:00") 
and [TOT] >= Time.FromText("10:31:00") 
then (if Duration.TotalMinutes( Time.FromText("11:00:00") - [TOT] ) < 30 and Duration.TotalMinutes( Time.FromText("11:00:00") - [TOT] ) > 1 then Duration.TotalMinutes( [TOT] - Time.FromText("10:30:00") ) else 
if (Duration.TotalMinutes( Time.FromText("11:00:00") - [VANAF] )) >= 30 then 30 else Duration.TotalMinutes( Time.FromText("11:00:00") - [VANAF] ))/30
else 0),
    #"11h00" = Table.AddColumn(#"10h30", "11:00", each if [VANAF] <= Time.FromText("11:29:00") 
and [TOT] >= Time.FromText("11:01:00") 
then (if Duration.TotalMinutes( Time.FromText("11:30:00") - [TOT] ) < 30 and Duration.TotalMinutes( Time.FromText("11:30:00") - [TOT] ) > 1 then Duration.TotalMinutes( [TOT] - Time.FromText("11:00:00") ) else 
if (Duration.TotalMinutes( Time.FromText("11:30:00") - [VANAF] )) >= 30 then 30 else Duration.TotalMinutes( Time.FromText("11:30:00") - [VANAF] ))/30
else 0),
    #"11h30" = Table.AddColumn(#"11h00", "11:30", each if [VANAF] <= Time.FromText("11:59:00") 
and [TOT] >= Time.FromText("11:31:00") 
then (if Duration.TotalMinutes( Time.FromText("12:00:00") - [TOT] ) < 30 and Duration.TotalMinutes( Time.FromText("12:00:00") - [TOT] ) > 1 then Duration.TotalMinutes( [TOT] - Time.FromText("11:30:00") ) else 
if (Duration.TotalMinutes( Time.FromText("12:00:00") - [VANAF] )) >= 30 then 30 else Duration.TotalMinutes( Time.FromText("12:00:00") - [VANAF] ))/30
else 0),
#"12h00" = Table.AddColumn(#"11h30", "12:00", each if [VANAF] <= Time.FromText("12:29:00") 
and [TOT] >= Time.FromText("12:01:00") 
then (if Duration.TotalMinutes( Time.FromText("12:30:00") - [TOT] ) < 30 and Duration.TotalMinutes( Time.FromText("12:30:00") - [TOT] ) > 1 then Duration.TotalMinutes( [TOT] - Time.FromText("12:00:00") ) else 
if (Duration.TotalMinutes( Time.FromText("12:30:00") - [VANAF] )) >= 30 then 30 else Duration.TotalMinutes( Time.FromText("12:30:00") - [VANAF] ))/30
else 0),
#"12h30" = Table.AddColumn(#"12h00", "12:30", each if [VANAF] <= Time.FromText("12:59:00") 
and [TOT] >= Time.FromText("12:31:00") 
then (if Duration.TotalMinutes( Time.FromText("13:00:00") - [TOT] ) < 30 and Duration.TotalMinutes( Time.FromText("13:00:00") - [TOT] ) > 1 then Duration.TotalMinutes( [TOT] - Time.FromText("12:30:00") ) else 
if (Duration.TotalMinutes( Time.FromText("13:00:00") - [VANAF] )) >= 30 then 30 else Duration.TotalMinutes( Time.FromText("13:00:00") - [VANAF] ))/30
else 0),
#"13h00" = Table.AddColumn(#"12h30", "13:00", each if [VANAF] <= Time.FromText("13:29:00") 
and [TOT] >= Time.FromText("13:01:00") 
then (if Duration.TotalMinutes( Time.FromText("13:30:00") - [TOT] ) < 30 and Duration.TotalMinutes( Time.FromText("13:30:00") - [TOT] ) > 1 then Duration.TotalMinutes( [TOT] - Time.FromText("13:00:00") ) else 
if (Duration.TotalMinutes( Time.FromText("13:30:00") - [VANAF] )) >= 30 then 30 else Duration.TotalMinutes( Time.FromText("13:30:00") - [VANAF] ))/30
else 0),
#"13h30" = Table.AddColumn(#"13h00", "13:30", each if [VANAF] <= Time.FromText("13:59:00") 
and [TOT] >= Time.FromText("13:31:00") 
then (if Duration.TotalMinutes( Time.FromText("14:00:00") - [TOT] ) < 30 and Duration.TotalMinutes( Time.FromText("14:00:00") - [TOT] ) > 1 then Duration.TotalMinutes( [TOT] - Time.FromText("13:30:00") ) else 
if (Duration.TotalMinutes( Time.FromText("14:00:00") - [VANAF] )) >= 30 then 30 else Duration.TotalMinutes( Time.FromText("14:00:00") - [VANAF] ))/30
else 0),
#"14h00" = Table.AddColumn(#"13h30", "14:00", each if [VANAF] <= Time.FromText("14:29:00") 
and [TOT] >= Time.FromText("14:01:00") 
then (if Duration.TotalMinutes( Time.FromText("14:30:00") - [TOT] ) < 30 and Duration.TotalMinutes( Time.FromText("14:30:00") - [TOT] ) > 1 then Duration.TotalMinutes( [TOT] - Time.FromText("14:00:00") ) else 
if (Duration.TotalMinutes( Time.FromText("14:30:00") - [VANAF] )) >= 30 then 30 else Duration.TotalMinutes( Time.FromText("14:30:00") - [VANAF] ))/30
else 0),
#"14h30" = Table.AddColumn(#"14h00", "14:30", each if [VANAF] <= Time.FromText("14:59:00") 
and [TOT] >= Time.FromText("14:31:00") 
then (if Duration.TotalMinutes( Time.FromText("15:00:00") - [TOT] ) < 30 and Duration.TotalMinutes( Time.FromText("15:00:00") - [TOT] ) > 1 then Duration.TotalMinutes( [TOT] - Time.FromText("14:30:00") ) else 
if (Duration.TotalMinutes( Time.FromText("15:00:00") - [VANAF] )) >= 30 then 30 else Duration.TotalMinutes( Time.FromText("15:00:00") - [VANAF] ))/30
else 0),
#"15h00" = Table.AddColumn(#"14h30", "15:00", each if [VANAF] <= Time.FromText("15:29:00") 
and [TOT] >= Time.FromText("15:01:00") 
then (if Duration.TotalMinutes( Time.FromText("15:30:00") - [TOT] ) < 30 and Duration.TotalMinutes( Time.FromText("15:30:00") - [TOT] ) > 1 then Duration.TotalMinutes( [TOT] - Time.FromText("15:00:00") ) else 
if (Duration.TotalMinutes( Time.FromText("15:30:00") - [VANAF] )) >= 30 then 30 else Duration.TotalMinutes( Time.FromText("15:30:00") - [VANAF] ))/30
else 0),
#"15h30" = Table.AddColumn(#"15h00", "15:30", each if [VANAF] <= Time.FromText("15:59:00") 
and [TOT] >= Time.FromText("15:31:00") 
then (if Duration.TotalMinutes( Time.FromText("16:00:00") - [TOT] ) < 30 and Duration.TotalMinutes( Time.FromText("16:00:00") - [TOT] ) > 1 then Duration.TotalMinutes( [TOT] - Time.FromText("15:30:00") ) else 
if (Duration.TotalMinutes( Time.FromText("16:00:00") - [VANAF] )) >= 30 then 30 else Duration.TotalMinutes( Time.FromText("16:00:00") - [VANAF] ))/30
else 0),
#"16h00" = Table.AddColumn(#"15h30", "16:00", each if [VANAF] <= Time.FromText("16:29:00") 
and [TOT] >= Time.FromText("16:01:00") 
then (if Duration.TotalMinutes( Time.FromText("16:30:00") - [TOT] ) < 30 and Duration.TotalMinutes( Time.FromText("16:30:00") - [TOT] ) > 1 then Duration.TotalMinutes( [TOT] - Time.FromText("16:00:00") ) else 
if (Duration.TotalMinutes( Time.FromText("16:30:00") - [VANAF] )) >= 30 then 30 else Duration.TotalMinutes( Time.FromText("16:30:00") - [VANAF] ))/30
else 0),
#"16h30" = Table.AddColumn(#"16h00", "16:30", each if [VANAF] <= Time.FromText("16:59:00") 
and [TOT] >= Time.FromText("16:31:00") 
then (if Duration.TotalMinutes( Time.FromText("17:00:00") - [TOT] ) < 30 and Duration.TotalMinutes( Time.FromText("17:00:00") - [TOT] ) > 1 then Duration.TotalMinutes( [TOT] - Time.FromText("16:30:00") ) else 
if (Duration.TotalMinutes( Time.FromText("17:00:00") - [VANAF] )) >= 30 then 30 else Duration.TotalMinutes( Time.FromText("17:00:00") - [VANAF] ))/30
else 0),
#"17h00" = Table.AddColumn(#"16h30", "17:00", each if [VANAF] <= Time.FromText("17:29:00") 
and [TOT] >= Time.FromText("17:01:00") 
then (if Duration.TotalMinutes( Time.FromText("17:30:00") - [TOT] ) < 30 and Duration.TotalMinutes( Time.FromText("17:30:00") - [TOT] ) > 1 then Duration.TotalMinutes( [TOT] - Time.FromText("17:00:00") ) else 
if (Duration.TotalMinutes( Time.FromText("17:30:00") - [VANAF] )) >= 30 then 30 else Duration.TotalMinutes( Time.FromText("17:30:00") - [VANAF] ))/30
else 0),
#"17h30" = Table.AddColumn(#"17h00", "17:30", each if [VANAF] <= Time.FromText("17:29:00") 
and [TOT] >= Time.FromText("17:31:00") 
then (if Duration.TotalMinutes( Time.FromText("18:00:00") - [TOT] ) < 30 and Duration.TotalMinutes( Time.FromText("18:00:00") - [TOT] ) > 1 then Duration.TotalMinutes( [TOT] - Time.FromText("17:30:00") ) else 
if (Duration.TotalMinutes( Time.FromText("18:00:00") - [VANAF] )) >= 30 then 30 else Duration.TotalMinutes( Time.FromText("18:00:00") - [VANAF] ))/30
else 0),
#"18h00" = Table.AddColumn(#"17h30", "18:00", each if [VANAF] <= Time.FromText("17:59:00") 
and [TOT] >= Time.FromText("18:01:00") 
then (if Duration.TotalMinutes( Time.FromText("18:30:00") - [TOT] ) < 30 and Duration.TotalMinutes( Time.FromText("18:30:00") - [TOT] ) > 1 then Duration.TotalMinutes( [TOT] - Time.FromText("18:00:00") ) else 
if (Duration.TotalMinutes( Time.FromText("18:30:00") - [VANAF] )) >= 30 then 30 else Duration.TotalMinutes( Time.FromText("18:30:00") - [VANAF] ))/30
else 0),
#"18h30" = Table.AddColumn(#"18h00", "18:30", each if [VANAF] <= Time.FromText("18:29:00") 
and [TOT] >= Time.FromText("18:31:00") 
then (if Duration.TotalMinutes( Time.FromText("17:30:00") - [TOT] ) < 30 and Duration.TotalMinutes( Time.FromText("19:00:00") - [TOT] ) > 1 then Duration.TotalMinutes( [TOT] - Time.FromText("18:30:00") ) else 
if (Duration.TotalMinutes( Time.FromText("17:30:00") - [VANAF] )) >= 30 then 30 else Duration.TotalMinutes( Time.FromText("19:00:00") - [VANAF] ))/30
else 0),
#"19h00" = Table.AddColumn(#"18h30", "19:00", each if [VANAF] <= Time.FromText("18:59:00") 
and [TOT] >= Time.FromText("19:01:00") 
then (if Duration.TotalMinutes( Time.FromText("19:30:00") - [TOT] ) < 30 and Duration.TotalMinutes( Time.FromText("19:30:00") - [TOT] ) > 1 then Duration.TotalMinutes( [TOT] - Time.FromText("19:00:00") ) else 
if (Duration.TotalMinutes( Time.FromText("19:30:00") - [VANAF] )) >= 30 then 30 else Duration.TotalMinutes( Time.FromText("19:30:00") - [VANAF] ))/30
else 0),
#"19h30" = Table.AddColumn(#"19h00", "19:30", each if [VANAF] <= Time.FromText("19:29:00") 
and [TOT] >= Time.FromText("19:31:00") 
then (if Duration.TotalMinutes( Time.FromText("20:00:00") - [TOT] ) < 30 and Duration.TotalMinutes( Time.FromText("20:00:00") - [TOT] ) > 1 then Duration.TotalMinutes( [TOT] - Time.FromText("19:30:00") ) else 
if (Duration.TotalMinutes( Time.FromText("20:00:00") - [VANAF] )) >= 30 then 30 else Duration.TotalMinutes( Time.FromText("20:00:00") - [VANAF] ))/30
else 0),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"19h30", {"DOTSID", "EVOID", "FRM", "AB", "WKN", "ORGANISATIE", "GEBOORTEDATUM", "TAAL", "KOSTENPLAATSCODE", "KOSTENPLAATSNAAM", "PROFIELTYPE", "DATUM", "VANAF", "TOT", "DUUR", "TOTALEDUUR", "TYPENAAM", "KOSTENPLAATSCODEPLANNING", "KOSTENPLAATSNAAMPLANNING", "PlanningActuals", "DimShop.ShopCodeShort", "CustomKEY"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "HalfHourSlot"}}),
    #"create timeKey" = Table.AddColumn(#"Renamed Columns", "TimeKey", each Date.ToText([DATUM], "yyyyMMdd") & "-" & [HalfHourSlot] & "-" & [DimShop.ShopCodeShort])
in
    #"create timeKey"