Date Format Issue

(0) Share

Report

Posted on by firda59

372

Hello,

I have date format issue when I want to get data from excel. My excel column has date format like dd-mmm-yy (10-Jun-23). I want to grab data to my Apps, I used this formula in my icon Reload:

ClearCollect(
ProductID,
Filter(
Table1,
DateValue(Text(Received, "dd-mmm-yyyy")) >= Today() - 90 && // 'Received Date' 3 months from now
IsBlank(Text('Delivery Date', "dd-mmm-yyyy")) && // 'Delivery Date' is blank
Value(No) <= 2000 // Limiting to 2000 rows
),
Sort(Table1, No, SortOrder.Descending)
)

But, I got this error message:

Error when trying to retrieve data from the network: Unable to match columns in the filtered view. Filtered columns count: 85, actual columns count: 87. To use ordering or filtering, please try to make all the columns visible. clientRequestId: e17979ef-ef63-4c7b-87a2-762d2d7641d1 serviceRequestId: f4bbc460-feff-43bd-acce-f3592af6ff4d;c692e537-6701-409c-a33b-3fee4d8c5a4e;e0b4163e-df21-4cb4-9e97-49cc1ca0dd32;da57f350-b2bc-43dc-957d-a7ffbbf853c0;c6872246-747b-47a2-a52c-5fbb2f3e30c3;d42fce84-3a1d-45c2-8132-78c4156da246

Can anyone help me to fix it? Thank you

Categories:

Building Power Apps

I have the same question (0)

All responses (12)

Answers (1)

Sort by

narayan225 2,547 Moderator on at

Like (0)

Report
Copy link

Link copied!

@firda59

When you receive date from Excel, you will need to convert them to actual values first as Excel stores dates as numbers.
http://powerappsguide.com/blog/post/convert-excel-date-serial-numbers

You will first need to convert them to date values that Canvas App recognises first.

Then your app should work fine.

Hope this helps.
Cheers!

Was this reply helpful? Yes No
firda59 372 on at

Like (0)

Report
Copy link

Link copied!

Hi @narayan225

So, it means that I should convert my date column in excel as number?

Was this reply helpful? Yes No
narayan225 2,547 Moderator on at

Like (0)

Report
Copy link

Link copied!

@firda59

You will first need to check how the date column is formatted. Try adding the table to a gallery and see how the date is shown if it is coming up as date, then it should be easier to filter with date values.

As your date is formatted like dd-mmm-yy (10-Jun-23), you will need to transform it to 10/06/2023 format and then only you can perform the filter operation.

Hope this helps.

Cheers!

Was this reply helpful? Yes No
firda59 372 on at

Like (0)

Report
Copy link

Link copied!

Hi @narayan225

I try to add my date in excel to gallery, this is the result:
In powerApps:

I dont understand why the result are different. Do you know why?

Was this reply helpful? Yes No
narayan225 2,547 Moderator on at

Like (0)

Report
Copy link

Link copied!
@firda59

Please use the following syntax for your filter condition.
ClearCollect( ProductID, Filter(Organisation, 'Date Joined' >= DateAdd(Now(),-3,TimeUnit.Months) && IsBlank('Delivery Date') && // 'Delivery Date' is blank Value(No) <= 2000 // Limiting to 2000 rows ))
This should work fine as I tested by creating a sample database with your schema.

Cheers!

Was this reply helpful? Yes No
firda59 372 on at

Like (1)

Report
Copy link

Link copied!

Hi @narayan225

In my combobox that has formula:
Distinct(ProductID, 'XXX ID'), error.

The error message:
Expected value '' to be a valid RFC 3339 'date-time' format. Allowed ISO 8601 format(s): 'YYYY-MM-DDThh:mm:ssZ', 'YYYY-MM-DDThh:mm:ss±hh:mm', 'YYYY-MM-DDThh:mm:ss'.

Do you know how to fix it?

Was this reply helpful? Yes No
narayan225 2,547 Moderator on at

Like (0)

Report
Copy link

Link copied!

@firda59

What are you trying to fill in the combo box?

And are you getting the error in the gallery or in the combo box?
Please advise.

Cheers!

Was this reply helpful? Yes No
firda59 372 on at

Like (1)

Report
Copy link

Link copied!
@narayan225

Hi, so I want to get at least 2000 the latest data from excel, which the column has blank delivery date and received date (today - 90days) 3 months ago. I used this formula in my button (refresh data to get 2000 latest data):
ClearCollect(
ProductID,
Filter(
Table1,
DateValue(Text(Received, "dd-mmm-yyyy")) >= Today() - 90 && // 'Received Date' 3 months from now
IsBlank(Text('Delivery Date', "dd-mmm-yyyy")) && // 'Delivery Date' is blank
Value(No) <= 2000 // Limiting to 2000 rows
),
Sort(Table1, No, SortOrder.Descending)
)

After my apps get that 2000 data, I used combobox to filter ID from that 2000 data. I used this formula:
Distinct(ProductID, 'XXX ID')

And then, my galery will show the component which has that ID (from combobox).

It is error after I click that button 'refresh'

Was this reply helpful? Yes No
narayan225 2,547 Moderator on at

Like (0)

Report
Copy link

Link copied!
@firda59

Did you use the syntax that I provided for the refresh button?
ClearCollect( ProductID, Filter(Organisation, 'Date Joined' >= DateAdd(Now(),-3,TimeUnit.Months) && IsBlank('Delivery Date') && // 'Delivery Date' is blank Value(No) <= 2000 // Limiting to 2000 rows ))
Did you use this syntax?

Cheers!

Was this reply helpful? Yes No
firda59 372 on at

Like (0)

Report
Copy link

Link copied!

@narayan225

yup, still error

I dont know but, when I try to get 1 column from Received, the date is different between Apps and Excel. But for another column date there is no issue. Is it affect to formula?

Was this reply helpful? Yes No