Forum Discussion

somears's avatar
somears
Frequent Visitor
7 years ago

DataFlow Azure Blob Storage Combine Binaries Multiple Csv Files

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"

Dataflow
Need Help

4 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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's avatar
      somears
      Frequent Visitor
      7 years ago

      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's avatar
        DataMountain
        Icon for Advocate I rankAdvocate I
        7 years ago

        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.