topic Re: Can Dataflow Gen2 read from lakehouse "Files" as a folder? in Dataflow

Can Dataflow Gen2 read from lakehouse "Files" as a folder?

Scott_Powell — Fri, 08 Sep 2023 22:29:36 GMT

Hi, I've uploaded several hundred daily Power BI audit logs into a Lakehouse under the Files area. I'd like to use a Dataflow Gen2 to combine the files and then reshape them. Is there a way to do this?

If I use a Lakehouse connection I seem to be able to select an individual file, but not the whole folder.

If I use a Folder connection, it wants to send everything through an on-prem gateway (which makes no sense).

Appreciate any insights - thanks in advance!

Scott

Re: Can Dataflow Gen2 read from lakehouse "Files" as a folder?

yjh — Sat, 09 Sep 2023 01:36:53 GMT

https://data-marc.com/2023/08/25/access-onelake-files-from-power-bi-desktop/

Re: Can Dataflow Gen2 read from lakehouse "Files" as a folder?

Anonymous — Sat, 09 Sep 2023 06:41:10 GMT

Hi yjh I realize I wasn't clear at all. I'm not trying to load them into Power BI desktop, I'm trying to use a Dataflow Gen2 to load them into tables in the Lakehouse.

Thanks,

Scott

Re: Can Dataflow Gen2 read from lakehouse "Files" as a folder?

yjh — Sat, 09 Sep 2023 16:31:10 GMT

The method is the same.

Re: Can Dataflow Gen2 read from lakehouse "Files" as a folder?

yjh — Sat, 09 Sep 2023 16:35:47 GMT

=Lakehouse.Content（null）

Then Drill down.

sorry for my poor English.

Re: Can Dataflow Gen2 read from lakehouse "Files" as a folder?

ZachRoberts — Fri, 06 Oct 2023 15:38:04 GMT

Hi Scott_Powell

Were you able to get this to work? Trying to do the same have a ton of files loaded into a folder in lakehouse and would like to use dataflow to shape them

Thanks,

Zach

Re: Can Dataflow Gen2 read from lakehouse "Files" as a folder?

Scott_Powell — Fri, 13 Oct 2023 13:55:18 GMT

Hi ZachRoberts , unfortunately I did not find a way to do this. If you do - please let me know, we could really use this.

Thanks,

Scott

Re: Can Dataflow Gen2 read from lakehouse "Files" as a folder?

ZachRoberts — Fri, 13 Oct 2023 14:19:04 GMT

Hi Scott_Powell ,

I wasn't able to figure out loading the files in my folder through Dataflow but ended up using a notebook to ingest the files to a table and went from there.

Below is the notebook details if you want to give it a shot:

The below loads all files from within a folder and removes the first row in each file (not sure if your files are delmited but below example my files are | delimited), and in the step where you can input the column names you don't have to do this for every column - you can select the columns you want to load you just have to provide the appropiate column # starting from 0

Cell 1

from pyspark.sql.functions import split, row_number, input_file_name from pyspark.sql import Window # Read all files from the directory into a DataFrame df = spark.read.text("Files/Expense/*.txt") # Add a column for the input file name df_with_filename = df.withColumn("filename", input_file_name()) # Add a row number to each row, partitioned by the input file name windowSpec = Window.partitionBy("filename").orderBy("value") df_with_rownum = df_with_filename.withColumn("rownum", row_number().over(windowSpec)) # Filter out the first row from each file df_filtered = df_with_rownum.filter(df_with_rownum.rownum > 1) # Split the value column by the | delimiter df_split = df_filtered.withColumn("split_values", split(df_filtered["value"], "\|")) # If you know the number of columns and want to give them names, you can do so # For example, if there are two columns: df_final = df_split.select( df_split["split_values"].getItem(0).alias("Constant"), df_split["split_values"].getItem(1).alias("BatchId"), ) # Show the resulting DataFrame df_final.show()

Cell 2

df_final.write.mode("overwrite").format("delta").saveAsTable("TableName")

Re: Can Dataflow Gen2 read from lakehouse "Files" as a folder?

joeguicearnold — Wed, 28 Feb 2024 22:41:56 GMT

This does not work for multiple files in the same folder though, right? You can only select a single file using this method as far as I an tell.

Re: Can Dataflow Gen2 read from lakehouse "Files" as a folder?

miguel — Wed, 28 Feb 2024 23:23:54 GMT

You can use the combine files experience the same as with other file system views (SharePoint, data lake, folder and others). If you're seeing any issues with this approach please create a separate topic / thread so we can take a closer look at it