Intro:
Welcome to the inaugural entry of “Bite-Sized Wizardry,” a series conjured for the citizen developers who are ready to weave their own digital spells. In today’s potion of knowledge, we delve into the mystical world of arrays – the foundational grimoires of data objects. Whether you’re a seasoned sorcerer of syntax or a novice in the arcane arts of code, mastering the manipulation of rows and columns is essential.
CSV data setup:
DOCUMENT NUMBER
SOURCE DOCUMENT TYPE
REQUISITION NUMBER
INPUT DATE
TOTAL AMOUNT
DEPARTMENT NUMBER
DEPARTMENT NAME
COST CENTER
COST CENTER NAME
DOCUMENT STATUS CODE
Status code
VOUCHED AMOUNT
Vendor ID
VENDOR NAME 1
VENDOR CITY
VENDOR STATE
21003077
P
4777
04/07/2021
143.90
7910
FLEET
7910
CENTRAL GARAGE
3
Canceled
143.90
VN0041
ALL STAR AUTOMOTIVE
DENHAM SPRINGS
LA
21002561
P
4018
03/23/2021
429.27
7910
FLEET
7910
CENTRAL GARAGE
0
Open
429.27
VN0357
ITA TRUCK SALES & SERVICE LLC
LAFAYETTE
LA
21005726
P
8350
07/06/2021
178.40
5070
POLICE
5070
CRIMINAL INVESTIGATION
2
Rejected
168.40
VN0052
AMAZON CAPITAL SERVICES INC
SEATTLE
WA
Transforming data input CSV data into array is super useful and this blog we will be exploring how to drop or add rows or columns
Tip 1:
Pick a CSV file in power automate the data of the file would be captured as a base64 encoding. The first step is to unpack the base64 and convert to string which could be readable
Tip 2:
Most of the CSV file would have CRLF (Carriage Return/Line Feed) “n” “rn” at the end of every record. Converting this to array would be leveraging split function. Here CLRF is a compose variable
This would convert each row in the CSV file as a string in an array
Tip 3:
Most of the CSV files do have header that highlights the data elements. For data manipulation we might have to drop the header row.
Tip 4:
If the CSV file from the source system has a trailer record in the file which has give meta data of no of records we might have to drop them as well while processing. We could use combination of reverse and skip function. First we reverse the order of an array and then remove the first element from that reversed array
Tip 5:
If I want to filter rows with a specific column value we would leverage the filter function
not(equals(item(), ”)),
equals(split(item(),’,’)?[10],’Open’)
)
This part splits the current item by commas using split(item(),’,’), then checks if the 11th element (index 10, as indexing starts at 0) is equal to the string ‘Open’.
