I'm not sure if its the M code that needs to be adjusted or the formatting but all of the rows won't load when I refresh my table in PowerQuery. How should I go about resolving the issue?
Mcode:
let
Source = Excel.CurrentWorkbook(){[Name="Paultable"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date ", type date}, {"Last Name", type text}, {"First Name", type text}, {"In", type time}, {"Out", type time}, {"In_1", type time}, {"Out_2", type time}, {"ID", type any}, {"Account", type any}, {"Pay Code ", type any}, {"Hours ", type any}, {"Days ", type any}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type", "Overtime", each if (Time.Hour([Out])-Time.Hour([In])+Time.Hour([Out_2])-Time.Hour([In_1]))>=8 and (Time.Minute([Out])-Time.Minute([In])+Time.Minute([Out_2])-Time.Minute([In_1]))>0 then Number.ToText((Time.Hour([Out])-Time.Hour([In])+Time.Hour([Out_2])-Time.Hour([In_1]))-8) & "." & Number.ToText((Time.Minute([Out])-Time.Minute([In])+Time.Minute([Out_2])-Time.Minute([In_1]))) else 0),
#"Added Custom" = Table.AddColumn(#"Added Custom1", "Regular Hours", each if [Overtime]=0 then 0 else 8),
#"Added Custom2" = Table.AddColumn(#"Added Custom", "Full Name", each [Last Name] & ", " & [First Name]),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Custom2", {"Date ", "Last Name", "First Name", "In", "Out", "In_1", "Out_2", "ID", "Account", "Pay Code ", "Hours ", "Days ", "Full Name"}, "Attribute", "Value"),
#"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] <> 0)),
#"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"Value", type number}})
in
#"Changed Type1"
Worksheet
Table
Mcode:
let
Source = Excel.CurrentWorkbook(){[Name="Paultable"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date ", type date}, {"Last Name", type text}, {"First Name", type text}, {"In", type time}, {"Out", type time}, {"In_1", type time}, {"Out_2", type time}, {"ID", type any}, {"Account", type any}, {"Pay Code ", type any}, {"Hours ", type any}, {"Days ", type any}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type", "Overtime", each if (Time.Hour([Out])-Time.Hour([In])+Time.Hour([Out_2])-Time.Hour([In_1]))>=8 and (Time.Minute([Out])-Time.Minute([In])+Time.Minute([Out_2])-Time.Minute([In_1]))>0 then Number.ToText((Time.Hour([Out])-Time.Hour([In])+Time.Hour([Out_2])-Time.Hour([In_1]))-8) & "." & Number.ToText((Time.Minute([Out])-Time.Minute([In])+Time.Minute([Out_2])-Time.Minute([In_1]))) else 0),
#"Added Custom" = Table.AddColumn(#"Added Custom1", "Regular Hours", each if [Overtime]=0 then 0 else 8),
#"Added Custom2" = Table.AddColumn(#"Added Custom", "Full Name", each [Last Name] & ", " & [First Name]),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Custom2", {"Date ", "Last Name", "First Name", "In", "Out", "In_1", "Out_2", "ID", "Account", "Pay Code ", "Hours ", "Days ", "Full Name"}, "Attribute", "Value"),
#"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] <> 0)),
#"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"Value", type number}})
in
#"Changed Type1"
Worksheet
Table