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.
Advocate I
Thank you so much for your explanation. I am going to test and tell you how it goes. Thank you so much for your time ! 🙂
Advocate ISorry Greg_Deckler
how do you do to name Source 1 and Source 2 ?
Source1 = Folder.Files("C:\temp\powerbi\csvs"),
Source2 = Folder.Files("C:\temp\powerbi\csvs1"),
Source = Table.Append(Source1,Source2),
Thanks
- Greg_Deckler
Community Champion
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.
- edhans
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
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!
