Skip to main content

Notifications

Community site session details

Community site session details

Session Id : oeZkoS/NUigbMNK1jiPnhA
Power Automate - Building Flows
Answered

Adding field conditionally to CSV file

Like (0) ShareShare
ReportReport
Posted on 20 Mar 2024 10:31:08 by 146,810 Most Valuable Professional

All,

I seeking some advice for a Flow where I am importing a CSV file (a credit card statement from a bank) and the last column (credit amount) is not present unless there is data in it. Currently, the users are manually entering zero and re-saving before importing. The rest of the flow works fine and I have attempted a possible solution (noted on the second screenshot) that did not work. Essentially all I want to do is include a zero if there is nothing present in the last column.

WarrenBelz_0-1710930447882.png

 

WarrenBelz_1-1710930496393.png

Any assistance would be greatly appreciated.

 

 

  • WarrenBelz Profile Picture
    146,810 Most Valuable Professional on 21 Mar 2024 at 23:29:45
    Re: Adding field conditionally to CSV file

    Hi @tom_riha ,

    Thanks very much for the effort here - I will analyse your structure and try implement it as it will be much more robust than the band aid, very low-tech process I came up with to get it going a short time ago. I am relying on the file to be consistent here, but in the samples I have, if there is data in the last column, there is none in the second last one, so I tested for an empty column 6. If it is empty, I can grab the column 7 value as it will be there and if not empty, I put a zero in column 7. 

  • Verified answer
    tom_riha Profile Picture
    10,085 Most Valuable Professional on 21 Mar 2024 at 18:00:22
    Re: Adding field conditionally to CSV file

    Hello @WarrenBelz ,

    I did a small PoC with a file as shown below which I suppose is in principle similar to your file.

    image.png

    1. Split it by the new line as you do, skip the first line. Use the output array in the 'Select' action. I'm using the uri representation of a new line instead of a variable, but it doesn't matter.

    image.png

    2. in that 'Select' remap all the columns to a custom property, in my case it's Title, Date, Credit

    Title is simple, you just split and index it as you do in the update. Item() in this case represents "for each item in the array".

    split(item(),';')[0]

    Then the date - you can quite easily change date format with the parseDateTime(...) expression as explained e.g. here in more detail. With your formats it might look as below.

    formatDateTime(parseDateTime(split(item(),';')[1], 'en-US', 'ddMMyyyy'), 'MMddyyyy')

     The last is the credit, again, just split and index. If there's a number it'll take the number, if not it'll be empty.

    split(item(),';')[2]

    image.png

    This will convert the contents of the CSV file into a JSON.

    image.png3. Now you have a JSON with array of object, you can loop through that array and create the items. Reference each of the properties using the key you defined in the 'Select' in the left column. The condition below should work for the Credit (now called Amount as I called it that way in the 'Select').

    if(empty(item()?['Amount']),'0', item()?['Amount'])

    Reference all the properties one by one using item()?['propertyName'] to create the item

    image.png

  • Ventit Profile Picture
    402 on 20 Mar 2024 at 22:31:29
    Re: Adding field conditionally to CSV file

    Try using null

     

    If(equals(Field),null),null, Field)

     

    I do that a lot with dates

  • WarrenBelz Profile Picture
    146,810 Most Valuable Professional on 20 Mar 2024 at 21:51:09
    Re: Adding field conditionally to CSV file

    Hi Tom,
    Thanks for the response - I am not a big Flow user when it comes to the more complicated things (I know it shows), but I still have the same conditional issue (only add a column if it does not exist already) with the split code. I am also struggling to get the Select code to create new records in SharePoint. I am using an Attachment control in Power Apps to grab a CSV file and need to create new records in SharePoint with two issues - the one I mentioned of conditionally adding the column and also have to "turn around" a date field from ddmmyyyy to mmddyyyy so that it works in SharePoint. 

    Is there a better way of doing all of this ?

  • tom_riha Profile Picture
    10,085 Most Valuable Professional on 20 Mar 2024 at 17:01:02
    Re: Adding field conditionally to CSV file

    Hello @WarrenBelz ,

    I'd try to add a middle step where you convert the CSV content into a JSON - after the 'Remove Blank Rows' use 'Select' where you remap all the columns into properties. Output from the 'Remove Blank Rows' as the 'From', each column (split(...)[x]) then mapped to a custom property name. That way you might get a better idea what's coming from the CSV, loop through the newly created JSON array and set the if(...) condition accordingly.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Warren Belz – Community Spotlight

We are honored to recognize Warren Belz as our May 2025 Community…

Congratulations to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard > Power Automate - Building Flows

#1
stampcoin Profile Picture

stampcoin 39

#2
Churchy Profile Picture

Churchy 20

#3
Chriddle Profile Picture

Chriddle 19 Super User 2025 Season 1

Overall leaderboard