I
Transform File.pdf (8.8 KB)
s there a best practice or rule of thumb to limit query dependencies in your data model? I am working with files from multiple ERPs, some of which have to be combined, to create lookup and fact tables. When I look at the dependencies it is a spider web. From a performance standpoint, they refresh and run just fine. One issue is that Transform from File XXX queries are showing up in the Query Editor. I have attached a screenshot for reference. Is there a way to turn off this feature?
HI @eric_m , while waiting for other members to jump in, I suggest using the forum search to discover if your query has been asked before by another member. Thanks!
@eric_m ,
I don’t recall seeing this question asked before, and unfortunately don’t have a good answer for you, but am looping in @Melissa , @Rajesh , @Greg, @haroonali1000 and @AntrikshSharma - the combination of whom should have some quality guidance for you on this one.
- Brian
Not in itself, I would say but let’s look into that…
Power Query will automatically generate these helper queries once you’ve selected.
New Source/Folder and chosen “Combine & Transform data”
In the Dependencies view, it will look something like this (output file indicated with check mark):
.
If instead you choose “Transform data” in the “Get Data” window, only a single query is created.
Of course if you still need to transform and append multiple files you could do that one by one, which in Dependencies view will look something like this (output file indicated with check mark).
.
Alternatively instead of connecting, transforming and appending muliple separate queries, you could write some M code and do it all in a single query (output file indicated with check mark).
From a performance standpoint I’d expect the 1st and 3rd method to do similar but the 2nd can potentionally get you in trouble…
This YT video illustrates the 3rd method, although not geard towards optimization or best practices
I hope this is helpful
3 Likes
Melissa,
Thank you for the great response. The folder mapped in the query will be the repository for monthly reports, which will need to be transformed for my reporting needs. Options 1 and 3 are the best solutions.
Many thanks for your help.
Eric
1 Like