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 - 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.
Hello edhans ,
Thank you very much !
This is what I wanted ! I can now import 1 Folder only with 2 types of .txt files that accept the 2 types of delimiters.
This is better for the users as they have to change 1 time the source path whenever they want to change the location of their .pbix file.
I accept your answer as your solution. Thank you for sending your example file, it helps to fully understand the process.
Have a great day !
xx
Thank you Greg_Deckler for your comments as well and helping for this issue.
I understand what you say in terms of what is the best solution in Query as we can encounter problems of latency if we do that.
In my case, I prefer this solution (import 1 Folder). But to confirm this, the idea would be to test and compare the 2 solutions in terms of time latency.