Cannot create currency field in CDS from Excel

[KED]

Trying to bring over data from Excel to CDS. Problem happening in the Create a new record CDS action. A currency field in Excel is not available as a dynamic value. It doesn't seem to matter how the field is formatted in Excel - text, number, currency - it still won't show up.

By the way, I have another flow to create a new record in another entity when a record is created in this entity. It needs to pass the Request Amount from the first entity to the Request Amount in the second entity. In that second flow, in the Request Amount field of the Create New Record action, I can pass the Request Amount (base) field of the first entity, but not the plain old Request Amount field. But in the first flow in the first entity, the Request Amount (base) field is not showing up.

[edgonzales · Accepted solution]

@KED 

Hi there.  It might work if you drop the output of your Request Amount into a Compose action before the CDS step, and then see if the output from the compose is available to use there.

Keep us posted.

-Ed

If you liked this reply, please give it a thumbs up! If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.

[v-litu-msft]

Hi @KED,

I assume that the Request Amount field format in the CDS is a string, and the Request Amount field in the Excel is integer caused the dynamic content not match. 

You could change the Request Amount field in CDS into an integer, or you could input expression into it:

item()?['Requested Amount']

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[KED]

@edgonzales thanks much - that worked for both currency fields, Request Amount and Annual Net Profit. @v-litu-msft thank you, and I wanted to mention that the Annual Net Profit field in Excel was formatted as text and the Request Amount field in Excel was formatted as number, but both were correctly passed to their respective CDS currency fields with the compose action. 

(By the way, I also tried compose with the Excel datetime field "Request Date" for the hell of it but still got the error saying CDS expected a datetime format and I was trying to pass it that kind of 43979.6504889236-looking number that Excel stores dates as. As a workaround I created text fields in Excel for date (=TEXT(AG2,"mm/dd/yyyy"), time (=TEXT(AG2,"h:mm AM/PM"), and datetime (=CONCAT([@[Request Date Text]]," ",[@[Request Time Text]]) and passed the Excel "Request Datetime Text" text field successfully to the "Request Date" datetime field in CDS.)

Writing a flow to bring in data from an Excel spreadsheet stored on SharePoint, as it is updated, seems to be such a common use case. It would be really nice if the CDS/Power Automate team could either make super clear documentation about these workarounds or if they could make CDS/Power Automate aware of Excel enough to avoid having to do these workarounds (i.e. if it's formatted as currency/date in Excel, it shows up as a dynamic value in a flow to a CDS currency/date field).... 🙏

This 👇 works