What is the best way to extract data from CSV files and insert to a Excel table

Question

Good day, I am having some difficulty with parsing information from CSV files in Power Automate (in order to automate a workflow). I am novice in Power Automate but have had success parsing very basic table structures. Now I have something a little more complicated and I am stuck. Can anyone help explain how I can do it in Power Query, or any alternative methods to achieve the same result are welcome also?

I have provided a picture of 2 examples of the CSV data. It is quite consistent but you can see that sometimes in the "Site Address" field for example, there is an extra line. This is the area where I am getting stuck, because I don't know how to work with any structure other than a straightforward table structure.

Also you can see that the number of products sometimes changes also, I am not sure how to allow for this either.

example of CSV format

My objective is to extract the necessary data and insert it into a table, like this:

Answer 1

Try this in powerquery

data .. get data ... from other sources ... blank query .. home ... advanced editor

paste in below, and name the query fix in the box on uppper right. File .. close and load to .. create connection only This is the code to process a single one of your CSV files

(t1 as table)=> let
#"Added Custom" = Table.AddColumn(t1, "Custom", each if ([Column1] <> "Product Name" and [Column3] <> null and [Column3]<>"") or [Column1]="Site Name:" or [Column1]="Order Number:" or [Column1]="Order Date:" then "keep" else "remove"),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = "keep")),
#"Added Custom1" = Table.AddColumn(#"Filtered Rows", "Site Name", each #"Filtered Rows"{0}[Column2]),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Order Number", each #"Filtered Rows"{1}[Column2]),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Order Date", each #"Filtered Rows"{2}[Column2]),
#"Renamed Columns" = Table.RenameColumns(#"Added Custom3",{{"Column3", "Product Code"}, {"Column4", "Quantity"}}),
#"Filtered Rows1" = Table.SelectRows(#"Renamed Columns", each ([Quantity] <> "" and [Quantity] <> null)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Column1", "Column2", "Column5", "Column6", "Custom"})
in #"Removed Columns"

data .. get data ... from other sources ... blank query .. home ... advanced editor

paste in below and edit the path to the appropriate directory. It will read in every CSV in that path and covert them into the format you want using the above function

//read all files in specified directory you fill in here
let Source = Folder.Files("C:\temp3\"),
#"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".csv")),
#"Added Custom1" = Table.AddColumn(#"Filtered Rows", "Custom", each Csv.Document(File.Contents([Folder Path]&"\"&[Name]),[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None])),
#"Added Custom" = Table.AddColumn(#"Added Custom1", "Fixed", each fix([Custom])),
#"Expanded Fixed" = Table.ExpandTableColumn(#"Added Custom", "Fixed", {"Product Code", "Quantity", "Site Name", "Order Number", "Order Date"}, {"Product Code", "Quantity", "Site Name", "Order Number", "Order Date"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Fixed",{"Content", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path", "Custom"})
in #"Removed Columns"