Forum Discussion
M language custom column between dates/timestamps
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,
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"
1 Reply
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"