When Importing Excel Table to use in PowerApp some columns not showing the data on ClearCollect() collection

[KMI-Keith]

I have imported Time Clock data from an excel table to run a collection to get the Sum() of the total work hours for that employee ID.

But for some reason I cannot get it to display the data of how many hours per row which is simple values and no formula etc.! I have tried changing the format of those columns to General/Text/Number and also copied just the raw values to another sheet, re-typed that column and for some reason my collection will not show any data for those columns!

Here is a screen shot of the excel:

I am using a collection on a Dropdown Box OnChange of: 

ClearCollect(colTimeClock, Filter(Table12, Value(EmployeeId)=Value(LblEmpID1.Text)))

and in the collection I am getting:

Which is odd to me that the columns are out of order and missing 2 columns besides the other 2 columns not showing the rows data there so what am I doing wrong?

[Drrickryp]

Hi @KMI-Keith 

Usually the reason is that there is a calculated column but it seems like you have made sure that this is not an issue.  Two hints, In your excel table  File > Info > Check For Issues , if there are any with a red !  fix them and try again. 

If that doesn't work,  create a blank app and add the Excel table as a data source and then add a list screen.  Hopefully, all of the columns will now be in the app. 

[KMI-Keith]

Only issue was personal info so I removed that before saving and shutdown the browser, opened a new session, created another blank app and its doing the same thing with this data.

I tried creating a Data Table and its showing the same exact data that collections is so it is definitely something with the Excel data but I cant figure out what it is. Im using the newest version of O365, Desktop apps and have done similar things from Excel before and havent seen where it is omitting columns and then omitting data in other columns.

[Drrickryp]

Hi @KMI-Keith 

I saw this once before and my fix was to Export the data to a SharePoint list and then save it from SharePoint as an Excel file. I didn't do anything to it in SharePoint other than export it.   I don't know why washing it through SharePoint worked but it did. 

[KMI-Keith · Accepted solution]

It didnt work for me, BUT 5% showed data! So I went back to the excel table and found which ones were showing the data and they were the only ones without the DateTime format being blank in those Clock_In and Clock_Out columns. So I deleted those 2 columns showing the Clock In and Clock Out times and all the columns to the right of where those used to be showed the correct data.

So something with the DateTime format of : 04/13/2020 06:23a screws up the import.

Thanks for helping me work through it, I appreciate it!