cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
svalen Member
Member

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

Accepted Solutions
MarcelBeug Super Contributor
Super Contributor

Re: Combine CSVs from Folder - Separate Function

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)
4 REPLIES 4
austinsense Established Member
Established Member

Re: Combine CSVs from Folder - Separate Function

This mess that it creates makes me cringe as well

Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast Smiley Happy
svalen Member
Member

Re: Combine CSVs from Folder - Separate Function

Are we the only ones? Smiley Very Happy

MarcelBeug Super Contributor
Super Contributor

Re: Combine CSVs from Folder - Separate Function

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)
svalen Member
Member

Re: Combine CSVs from Folder - Separate Function

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

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 113 members 1,687 guests
Please welcome our newest community members: