Forum Discussion
How to combine Folders
Sky571 - see if this helps. It is not the way I would ultimately go about this as it is more difficult to maintain. I tried it using the two table transformation approach with a Table.Combine at the end and that is much more managable long term with the same results. However, this is with a single query, plus all of the Combine magic that Power Query does.
let Source = Folder.Files("C:\Users\Ed Hansberry\OneDrive\Work Stuff\Power BI Forum Examples\Test Files"), #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Folder Path], "Import")), #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each ([Extension] = ".txt")), #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows1", each [Attributes]?[Hidden]? <> true), #"Invoke Custom Function Tab" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File Tab", each #"Transform File Tab"([Content])), #"Invoke Custom Function Pipe" = Table.AddColumn(#"Invoke Custom Function Tab", "Transform File Pipe", each Table.PromoteHeaders(#"Transform File Pipe"([Content]), [PromoteAllScalars=true])), #"Added Correct Delimiter" = Table.AddColumn(#"Invoke Custom Function Pipe", "Correct Delimiter", each if Table.ColumnNames([Transform File Pipe]){1} = "JournalLib" then [Transform File Pipe] else [Transform File Tab]), #"Renamed Columns1" = Table.RenameColumns(#"Added Correct Delimiter", {"Name", "Source.Name"}), #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1",{"Source.Name", "Correct Delimiter"}), #"Expanded Correct Delimiter" = Table.ExpandTableColumn(#"Removed Other Columns1", "Correct Delimiter", {"JournalCode", "JournalLib", "EcritureNum", "EcritureDate", "CompteNum", "CompteLib", "CompAuxNum", "CompAuxLib", "PieceRef", "PieceDate", "EcritureLib", "Debit", "Credit", "EcritureLet", "DateLet", "ValidDate", "Montantdevise", "Idevise", "DateRglt", "ModeRglt", "NatOp", "IdClient"}, {"JournalCode", "JournalLib", "EcritureNum", "EcritureDate", "CompteNum", "CompteLib", "CompAuxNum", "CompAuxLib", "PieceRef", "PieceDate", "EcritureLib", "Debit", "Credit", "EcritureLet", "DateLet", "ValidDate", "Montantdevise", "Idevise", "DateRglt", "ModeRglt", "NatOp", "IdClient"}) in #"Expanded Correct Delimiter"This does not tell the whole story though, so rather than post all of the precedent queries and functions, take a look at my PBIX attached. You will need to change the source directories to yours of course for this to work.
It provides this. You'll need to manually apply the Change Type step after this, and get rid of the blank rows. Many of your sample files only had a header row with no detail rows. Those get loaded. Just filter out nulls in the JournalCode field.How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
Sky571 - You have to open up Advanced Editor and type in the code. Power Query is just code and the graphical user interface is just a way to write that code by clicking buttons. But, for tricky things, you still need to open up the Advanced Editor and write the code yourself.
Sky571 Maybe I am missing something. Why try to dynamically determine the delimiter? If you know your files have a comma as delimiter in one file and a pipe in the other (whatever they are), then just do the combine operation on them separately.
That will generate 2 queries.
Right-click both of those and uncheck Enable Load.
Now, on the first query, select Append as New, then add the second query to it.
One refresh will grab both sets of files and return one final query that you can then Enable Load and return to the DAX model.
What am I missing?
Greg_Deckler - understood. But unless someone has some query magic that will dynamically determine the delimiter then apply the correct one, this will require two transformations, one for each delimiter. From a performance standpoint, I will need to see hard proof that doing it this way is more performant than doing two transformations then a single combine operation. From a maintenance standpoint, this single transformation approach is much more tedious to maintain as you must use the Advanced Editor and manually write and adjust functions and code. The dual transformation approach + Table.Combine() (append) can be done 100% through the PQ UI and is much easier to maintain and understand, and I would argue is just as performant if not more so than a lot of the custom code necessary to do this in one transformation step.
We will see. The logic of "one transformation is better" is often the best approach, but not always, and this is one case where I do not think it is the case.edhans - If I understand what Sky571 is trying to do, and I could be mistaken, I believe the concern is having to keep the same transformation code in 2 different sets of queries and the way I read the post, I think he only wants to have a single set of code doing the transformation for both sets of files. Makes sense from a maintenance point of view. However, I may have 100% missed the mark so always appreciate additional eyes and thoughts!
edhans - Yeah, I'm good with that. My thought was to maintain 2 folders. Append the Folder queries into a single list and based upon the folder, invoke one of two functions that either ingests based upon " " separator or "|" separator and these 2 functions pass back the same table to be fed into the rest of the query transformations. But, the Binary column is causing me fits trying to get it to work.
Hello edhans and Greg_Deckler ,
Thank you for searching.
Yes I do need a single code doing the transformation for both set of files.
I did some tests and it does not work as I would though.
At the moment, I import 2 Folders with :
- Files (.txt) with " " delimiters
- Files (.txt) with "I " delimiters
I would like to import 1 Folder only and say in parameters that it can handle both delimiters => do you think it is possible or I need to import 2 folders ?
Here is my sample files : https://drive.google.com/file/d/1OBcUymgK8flIg5IhCKX6eLN-02V-Y80o/view?usp=sharing
Thank you for your time !
