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 ChampionHello Sky571
the idea is to load every txt at once (putting every file into one folder or to load a superior folder) and then search for delimiter tab or pipe and depending on the outcome apply the CSV-Function with adapted delimiter. I hope I was clear enough 🙂
Here my example
let
Source = Folder.Files("YourFolderWithAllTXTFiles"),
Countlimiter= (BinaryFile, Limiter)=>
let
Source = Csv.Document(BinaryFile,null,null,null,null),
CountLimiter = List.Sum(List.Transform(Source[Column1], each Text.PositionOfAny(_,{Limiter})))
in
CountLimiter,
AddExtractDependingOnLimiter = Table.AddColumn
(
Source,
"DataTXT",
(add)=>
let
IsTab = if Countlimiter(add[Content]," ")>Countlimiter(add[Content], "|") then true else false,
ExtractTXT = if IsTab = true then Csv.Document(add[Content],[Delimiter=" ", Encoding=1200, QuoteStyle=QuoteStyle.None]) else Csv.Document(add[Content],[Delimiter="|", Encoding=1200, QuoteStyle=QuoteStyle.None]),
PromoteHeaders = Table.PromoteHeaders(ExtractTXT)
in
PromoteHeaders
),
CombineTable = Table.Combine(AddExtractDependingOnLimiter[DataTXT])
in
CombineTable
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Advocate IHello Jimmy801 ,
I tried your solution and it doesn't work because of the different columns I have.
My example file has 22 columns.
The others between 18 and 22 columns.
and I want the other files keep the same numbers of columns as my example files (22) with the same PromoteHeaders.
Other than that, I think your solution would work as well.
I added this information in the Query code : [Delimiter=" ", Columns=22, Encoding=1200, etc..." int both ways, but nope.
Thank you