Power query to import multiple parquet files, combine into one dataset and perform transformations

[Syndicate_Admin]

Looking to build a power query in dataflow gen2 that looks at a lakehouse to loop through parquet files to create one single combine dataset and then perform transformation for it. I cannot use the fabric pipeline DF cause the columns of interest in the parquet files exceeds the limit of characters of a column. The end goal is load all parquet files into dataflow gen2 and then split the long column to smaller separate columns so it can be loaded into fabric warehouse. My current code can dynamically navigate through lake house based on date function and open one row of parquet file. Looking for mechanism to loop through all the files and combine into one query or data set that i can perform transformations on. here is current query below

let
  Source = Lakehouse.Contents(null){[workspaceId = "cxxxxxxx"]}[Data]{[lakehouseId = "xxccccccccccc"]}[Data],
  Navigation = Source{[Id = "Files", ItemKind = "Folder"]}[Data],
  #"Navigation 1" = Navigation{[Name = "fanso_test"]}[Content],
  #"Navigation 2" = #"Navigation 1"{[Name = "fans_b"]}[Content],
  #"Navigation 3" = #"Navigation 2"{[Name = "first" & Number.ToText(Date.Year(Date.From(DateTime.LocalNow())))]}[Content],
  #"Navigation 4" = #"Navigation 3"{[Name = "second" & Number.ToText(Date.Month(Date.From(DateTime.LocalNow())))]}[Content],
  #"Navigation 5" = #"Navigation 4"{[Name = "third" & Number.ToText(Date.Day(Date.AddDays(Date.From(DateTime.LocalNow()), -1)))]}[Content],
  //#"Imported Parquet" = Parquet.Document(Table.Column(#"Navigation 5",[Content]))
  #"Loop Rows" = Table.Column(#"Navigation 5","Content"),
  #"Navigation 6" = #"Loop Rows"{1},
  #"Imported Parquet" = Parquet.Document(#"Navigation 6"),
  #"Choose columns" = Table.SelectColumns(#"Imported Parquet", {"pid", "tranx", "items", "fandatetime", "created", "updated", "kick_id", "received", "fan_time", "local_fan_date", "isactive", "infk_id", "loaddedtime"}),
  #"Split column by delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Choose columns", {{"items", Splitter.SplitTextByDelimiter("^", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "items"),
  #"Split column by delimiter 1" = Table.SplitColumn(#"Split column by delimiter", "items", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"items.1", "items.2", "items.3", "items.4", "items.5", "items.6", "items.7", "items.8"}),
  #"Changed column type" = Table.TransformColumnTypes(#"Split column by delimiter 1", {{"items.1", type text}, {"items.2", Int64.Type}, {"items.3", type number}, {"items.4", type text}, {"items.5", type text}, {"items.6", type text}, {"items.7", type text}, {"items.8", type number}})
in
    //#"Imported Parquet"
    #"Changed column type"

 - any help would be very much appreciated. 

[Syndicate_Admin]

Hi @adahao08 

May I know if the following step gives you a column with all files you want to loop? 

#"Loop Rows" = Table.Column(#"Navigation 5","Content"),

If so, we can try creating a new custom function (query) with the rest steps after that, similar to below. This function is to get data from a parquet file at a time. 

(paraFile)=>
let
  #"Navigation 6" = paraFile , // this is a file parameter
  #"Imported Parquet" = Parquet.Document(#"Navigation 6"),
  #"Choose columns" = Table.SelectColumns(#"Imported Parquet", {"pid", "tranx", "items", "fandatetime", "created", "updated", "kick_id", "received", "fan_time", "local_fan_date", "isactive", "infk_id", "loaddedtime"}),
  #"Split column by delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Choose columns", {{"items", Splitter.SplitTextByDelimiter("^", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "items"),
  #"Split column by delimiter 1" = Table.SplitColumn(#"Split column by delimiter", "items", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"items.1", "items.2", "items.3", "items.4", "items.5", "items.6", "items.7", "items.8"}),
  #"Changed column type" = Table.TransformColumnTypes(#"Split column by delimiter 1", {{"items.1", type text}, {"items.2", Int64.Type}, {"items.3", type number}, {"items.4", type text}, {"items.5", type text}, {"items.6", type text}, {"items.7", type text}, {"items.8", type number}})
in
    //#"Imported Parquet"
    #"Changed column type"

Then in the original table that has a column which includes all files, invoke above custom column as a new column. This should give you a column which has a table result on each row. Then expand this column to see data of all tables. 

Using custom functions in Power Query - Power Query | Microsoft Learn

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[Syndicate_Admin]

Thanks for the response, sorry i didnt add more context. The plan is to add dataflow to a pipeline to run on a schedule so its automated. The logic here is for dataflow to loop through the number of parquet files for each pipeline run (this number of files varies), combine all the files into one dataset, and then perform some transformations to the entire dataset and load into a fabric warehouse without any manual intervention. 

To answer your question, the step "Loop Rows" creates a list like this with the binary of each parquet file to be looped and combined.

The reason why i am doing it this way is that currently it doesnt look like you can pass parameters to a dataflow in a pipeline. If that was possible, I could just pass the filename from the pipeline to the dataflow and will like a for each loop to perform the dataflow transformation and load. If you are aware of a better way to do this, please let me know. Thanks

[Syndicate_Admin]

Out of curiosity - what produced these Parquet files? Might be easier to ask that source system for a more suitable format.

The Delta tables in Fabric are supposed to be queried in place, not imported again.