DataFlow Azure Blob Storage Combine Binaries Multiple Csv Files

[somears]

Is their a way to consume multiple csv files from a Azure Blob storage container using DataFlows? All the files in the blob are of the same format. I’ve successfully created a DataFlow and it is connecting to the Azure Blob storage and exposing one file – below query. The query editor doesn’t seem to support the ability to combine binaries. The desktop Power BI has this functionality but it looks like it creates a custom function to make this happen? Has anyone managed to make this work?

let

Source = AzureStorage.Blobs("bireporting"),

Navigation = Source{[Name = "reports"]}[Data],

#"Filtered rows" = Table.SelectRows(Navigation, each Text.StartsWith([Name], "852/")),

#"Navigation 1" = #"Filtered rows"{[#"Folder Path" = "https://bireporting.blob.core.windows.net/reports/", Name = "852/ 852_20190113_20190119_0001.txt"]}[Content],

#"Imported Csv" = Csv.Document(#"Navigation 1"),

#"Split column by delimiter" = Table.SplitColumn(#"Imported Csv", "Column1", Splitter.SplitTextByDelimiter("|"), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9", "Column1.10"})

in

#"Split column by delimiter"

[v-lili6-msft]

hi, @somears 

For your code, if you need to add a "Expand columns" step after #"Navigation 1"

If not your case, please share some sample data and expected output.

Best Regards,

Lin

[somears]

Thank you for your response.

Here is some test data that I'm working with. Each file is pipe delimeted with 10 columns. The blob storage will have hundreds of these files.

TestFile1.csv
AMAZ05|20190113|20190119||16.07|C6PATCH1WH|B00016W704||QA|17
AMAZ05|20190113|20190119||16.07|C6PATCH1WH|B00016W704||QP|0
AMAZ05|20190113|20190119||16.07|C6PATCH1WH|B00016W704||QS|1

TestFile2.cvv
AMAZ05|20190113|20190119||16.07|C6PATCH1WH|B00016W704||QU|0

AMAZ05|20190113|20190119||16.07|C6PATCH1WH|B00016W704||QE|17

Proof of concept

Create a dataflow in the PowerBI service that will combine all the files in the Azure blob storage container. No additional transformations need to happen. Just expose the below files as 10 columns in the dataflow. Where I'm stuggling is that I don't see a "Combine File" option in the Content column like I see when I'm in the desktop version (left arrow). I do see a Combine tables dropdown option in the tool bar (right arrow). I'm not sure how to use that to combine the tables.

Thank you for your help

See attached picture below from the power query window in Power BI service Dataflow design.

[DataMountain]

This feature isn't available yet and I will be posting an idea.

If you have a finite number of files, you can utilize the "Duplicate Query" option to obtain all of the files, then use the "Append Queries as New" option in your picture.

[sumesh22]

Hi everyone,

Any idea regarding the above Problem as I'm stuck with the same issue.