Forum Discussion
Advocate IHow 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.
Community Championedhans - 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!
Community ChampionGreg_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.
- Greg_Deckler
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.