All of my rows won't load

PWil84

New Member
Joined
May 20, 2021
Messages
41
Office Version
  1. 2019
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
1667854184914.png

Table
1667854217622.png
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
I'm not all together sure about Bryant's entry on 10/25 isn't being added, but I suspect that Silvestre and Fernando are being filtered out due to their entries only having an In time. Have a look in Power Query Editor, and select the "Added Custom1" step. Then look for Silvestre and Fernando...is their overtime equal to 0, null, error, or other? Based on that, I would update your "Added Custom" column for Regular Hours to be if [Overtime]=0 or null then 0 else 8
 
Upvote 0

Forum statistics

Threads
1,223,516
Messages
6,172,778
Members
452,477
Latest member
DigDug2024

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top