Dataflow Gen2 & Multiple CSV Files

[MJWilliams]

I've just started using Fabric and have almost zero programming knowledge so please forgive the naivety of my question.

I have a scenario where csv files are uploaded to Fabric via OneLake file explorer. The format of the csv files is the same, they are copied into OneLake on a daily basis, into seperate folders.

My intention is to convert this data into tables for analysis. Via the data engineering tab, I select Dataflow Gen 2 to open Power Query and then get data. From there I select my Lakehouse and the folder where the csv files are contained. Power Query then brings all of the file data into a table but there isn't the option the transform the data as there would be via Power Query in Excel. I'm stuck with a list of file data.



I've tried researching how to move past this step and chatgpt suggested that I edit the code in the advanced editor to merge the csv files, an example of which is below.

let
// Load the list of files from the Lakehouse
Source = Lakehouse.Contents(null){[workspaceId = "c9293779-1a6f-4306-928a-9e52e62e24f1"]}[Data]{[lakehouseId = "5153f2a6-80a2-4a7b-9b0b-a6a5c6260ef9"]},

// Filter to only include CSV files
#"Filtered Files" = Table.SelectRows(Source, each Text.EndsWith([Name], ".csv")),

// Keep only the Content column for now
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Files", {"Content"}),

// Parse CSV content
#"Parsed CSV Content" = Table.AddColumn(#"Removed Other Columns", "Parsed CSV",
each try
Csv.Document([Content])
otherwise null
),

// Remove any rows where parsing failed
#"Removed Failed Parsing" = Table.SelectRows(#"Parsed CSV Content", each [Parsed CSV] <> null),

// Check if the parsed data is a list (which indicates it might be records rather than a table)
#"Check If List" = Table.AddColumn(#"Removed Failed Parsing", "Is List", each Value.Is([Parsed CSV], type list)),

// Convert lists to tables if necessary
#"Convert Lists to Tables" = Table.TransformColumns(#"Check If List",
{"Parsed CSV",
each if [Is List] then Table.FromList(_, Splitter.SplitByNothing()) else _}
),

// Expand the CSV data into columns
#"Expanded CSV Data" = Table.ExpandTableColumn(#"Convert Lists to Tables", "Parsed CSV", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8"})
in
#"Expanded CSV Data"

I've tried a number of different variations of this provided by chatgpt but all seem to be returning similar error messages, one of which is copied below.

---------- Message ----------
Expression.Error: We cannot convert a value of type Record to type Table.

---------- Session ID ----------
19f1a599-eaba-4cc2-b502-098be33fc49c

---------- Request ID ----------
426abfec-e3d1-4bed-90db-e3bc076cd9c3

---------- Mashup script ----------
section Section1;
shared Export1_Price = let
// Load the list of files from the Lakehouse
Source = Lakehouse.Contents(null){[workspaceId = "c9293779-1a6f-4306-928a-9e52e62e24f1"]}[Data]{[lakehouseId = "5153f2a6-80a2-4a7b-9b0b-a6a5c6260ef9"]},

// Filter to only include CSV files
#"Filtered Files" = Table.SelectRows(Source, each Text.EndsWith([Name], ".csv")),

// Keep only the Content column for now
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Files", {"Content"}),

// Parse CSV content
#"Parsed CSV Content" = Table.AddColumn(#"Removed Other Columns", "Parsed CSV",
each try
Csv.Document([Content])
otherwise null
),

// Remove any rows where parsing failed
#"Removed Failed Parsing" = Table.SelectRows(#"Parsed CSV Content", each [Parsed CSV] <> null),

// Check if the parsed data is a list (which indicates it might be records rather than a table)
#"Check If List" = Table.AddColumn(#"Removed Failed Parsing", "Is List", each Value.Is([Parsed CSV], type list)),

// Convert lists to tables if necessary
#"Convert Lists to Tables" = Table.TransformColumns(#"Check If List",
{"Parsed CSV",
each if [Is List] then Table.FromList(_, Splitter.SplitByNothing()) else _}
),

// Expand the CSV data into columns
#"Expanded CSV Data" = Table.ExpandTableColumn(#"Convert Lists to Tables", "Parsed CSV", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8"})
in
#"Expanded CSV Data";

With my limited experience it's difficult to understand where the issue lies, can anyone help with advising if what I'm trying to do is the best method and perhaps where the problem is?

KInd regards,

Martin.

[MJWilliams]

The links open the individual csv files, data all looks fine but not sure how to merge them all from within Fabric.

[lbendlin]

Then you should be able to use a Table.Combine - PowerQuery M | Microsoft Learn  over the [Content] Column

[MJWilliams]

Thanks, I'll have a look at this and see if I can get it to work.

[lbendlin]

Expand the "Content" column.

[MJWilliams]

Hi,
I've tried this but only a select all tick box is available, it won't let me tick it.

[lbendlin]

What happens when you click one of the [Table]  links?