cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AppuKuttan
New Member

Lookup date column from excel

Dear all

I am trying to lookup a date from an excel list which is formatted as a date column - but for some reason I cannot get it to work. I suspect this is because excel stores dates as numbers and when i save it inside powerapp collection it is stored as below :-

AppuKuttan_0-1627154990789.png

 

AppuKuttan_1-1627154990367.png

 

LookUp(ExcelTabel,Internalnumber="5954305501",Text(Plannedstart,"dd-mm-yyy"))

I also tried 

LookUp(ExcelTabel,Internalnumber="5954305501",Plannedstart)

 

I tried many other things but nothing is working 

 

Thanking in advance for your help 🙂

2 REPLIES 2
GarethPrisk
Super User II
Super User II

Your screenshot of the collection shows that the Plannedstart column is blank.

How is that collection being formed?

Is ExcelTabel a collection, or another data source (Excel connection, static Excel table)?

 

Your LookUp formula is also assuming Internalnumber is a text value, and it appears to be numeric.

 

Also, the column headers in Excel don't match the column names of your collection screenshot, or formulas. Again, how are you deriving the collection?

 

 

Also - this is one of multiple identical posts. Asking the same question multiple times will not increase the likelihood that the questions is answered. In some cases, people may assume it's spam. 😁

It does look like you said that the forum was saying your posts were spam. Sorry for any confusion, or if the forums were misbehaving.

AppuKuttan
New Member

Hello @GarethPrisk ,

 

Thanks for replying back to me. The forum was acting up 🙂 was marking all my post as spam including this one. I ended up creating a new account to post my question but it marked that question (this one) as spam too lol.

 

Regarding your question. I am using the function ClearCollect in the OnStart property to create ExcelTabel collection. 

ClearCollect(Exceltable,Table1)

Here Table1 refers to the static excel table data connection

 

Wanted to ask you a question

  • To fix the issue temporarily i used the static excel connection since i have around 8k records. For some reason whenever i try to add a excel data connection i have the below error 😞 . I also noted that if i used onedrive for business connector things worked perfectly but it can lookup max 2000 records and not more than that. Could you suggest a connector that i can use which would be able to refresh the data.... since for the static connector I need to manually link the file each time which i a big pain 😞

AppuKuttan_0-1627500118386.png

 

Thanks a lot for your help 🙂

 

Tagging my original account

@Anonymous_Hippo 

 

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,337)