Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Combine CSVs from Folder - Separate Function

Hello all.

 

I have a folder containing *.csv files that I need to import.

 

When I do that, BI creates a separate folder on the queries panel with a function and other files (please see the pictures below).

 

For the sake of compactness and organization, is there a way to put all the folder inside the query I am going to invoke the function in? I have tried analyzing and adapting the code inside the files from "Transform Binary from Data" but I didn't succeed.

 

With the change I am looking for the result would be the same but I think it would look better. Am I obsessive or something? Cat LOL Man LOL Robot LOL Smiley LOL Woman LOL

 

1.jpg2.jpg

 

 

1 ACCEPTED SOLUTION
MarcelBeug
Community Champion
Community Champion

As a matter of fact (or a matter of opinion?) these objects cooperate just fine:

one of your files is the Sample Binary in binary form,

the parameter Sample Binary Parameter1 is pointing to the Sample Binary,

the "Transform Sample Binary from Data" is an example query that transforms your Sample Binary to a table,

the function Transform Binary From Data is actually the example query wrapped in a function.

 

Any modifications in the example query will be automatically promoted to the function.

 

The function has one parameter with exactly the same name as the parameter that is pointing to the Sample Binary.

So in the example query that parameter is pointing to the Sample Binary, but in the function it points to the parameter that is supplied to the function being the contents of the files in your folder (as you can see in the step "Invoke Custom Function1" step in your query). So the parameter is just a trick so the exact contents of the example query can be wrapped in  the function without any modification.

 

In the "Removed Other Columns" step, other columns - but the filename and your tables - are removed from you navigation table (or in other words: your file list). If you want other columns from your navigation table (e.g. "Date Created") you can modify this step using the small wheel and (un)check the columns you (don't) want.
Note: for this step to work fine, don't remove columns from your navigation table before you choose "Combine Binaries".

 

In the "Expand Table Column1" step, your tables are expanded,

 

So the place to make modifications to individual files is the example query, except for any data type changes as these will get lost in the last step that is added to the query in which the tables are expanded.

I raised 2 ideas regarding this subject:

Make "combine binaries" independent from existence of a particular file

Preserve data types when expanding table columns

 

and I created a 21 minute video in which Excel files are combined using this functionality (you can jump to various parts of the video via links that are supplied below the video).

Specializing in Power Query Formula Language (M)

View solution in original post

4 REPLIES 4
MarcelBeug
Community Champion
Community Champion

As a matter of fact (or a matter of opinion?) these objects cooperate just fine:

one of your files is the Sample Binary in binary form,

the parameter Sample Binary Parameter1 is pointing to the Sample Binary,

the "Transform Sample Binary from Data" is an example query that transforms your Sample Binary to a table,

the function Transform Binary From Data is actually the example query wrapped in a function.

 

Any modifications in the example query will be automatically promoted to the function.

 

The function has one parameter with exactly the same name as the parameter that is pointing to the Sample Binary.

So in the example query that parameter is pointing to the Sample Binary, but in the function it points to the parameter that is supplied to the function being the contents of the files in your folder (as you can see in the step "Invoke Custom Function1" step in your query). So the parameter is just a trick so the exact contents of the example query can be wrapped in  the function without any modification.

 

In the "Removed Other Columns" step, other columns - but the filename and your tables - are removed from you navigation table (or in other words: your file list). If you want other columns from your navigation table (e.g. "Date Created") you can modify this step using the small wheel and (un)check the columns you (don't) want.
Note: for this step to work fine, don't remove columns from your navigation table before you choose "Combine Binaries".

 

In the "Expand Table Column1" step, your tables are expanded,

 

So the place to make modifications to individual files is the example query, except for any data type changes as these will get lost in the last step that is added to the query in which the tables are expanded.

I raised 2 ideas regarding this subject:

Make "combine binaries" independent from existence of a particular file

Preserve data types when expanding table columns

 

and I created a 21 minute video in which Excel files are combined using this functionality (you can jump to various parts of the video via links that are supplied below the video).

Specializing in Power Query Formula Language (M)
Anonymous
Not applicable

Thanks Marcel.

 

I guess that what I was looking for is not possible but you helped me to understand the "mechanics" of this functionality! Smiley Happy

 

Regards,

 

Samuel

austinsense
Impactful Individual
Impactful Individual

This mess that it creates makes me cringe as well

Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast 🙂
Anonymous
Not applicable

Are we the only ones? Smiley Very Happy

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.