Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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?
Solved! Go to Solution.
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).
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).
Thanks Marcel.
I guess that what I was looking for is not possible but you helped me to understand the "mechanics" of this functionality!
Regards,
Samuel
This mess that it creates makes me cringe as well
Are we the only ones?
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
102 | |
99 | |
38 | |
37 |
User | Count |
---|---|
158 | |
125 | |
76 | |
74 | |
63 |