Forum Discussion
How to combine Folders
Hello,
I would like to combine the importation of 2 Folders which have Files (txt) with the same numbers of columns and names , except:
- 1 has the delimiter " "
- the other the delimiter " I "
At the moment I have to refresh 2 folders every time I have to copy and change location of my .pbix file . It is very time-consuming.
See what appears with 2 requests :
Is there a way to fix the parameters beforehand, so that I would have only 1 Folder to refresh (but it will go and search the datas to 2 folders)
Thank you for your answer xx
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.
22 Replies
Sky571 So...maybe but I'm not entirely certain I understand what you are asking. Are you saying that you want to combine all of the files in a single folder and have the query figure out which delimiter each file has or are you saying you want one Folder query that really reaches out to 2 different folders and makes the necessary correction?
Hello Greg_Deckler ,
Thank you for answering quickly.
I think the 1st option is what I would like : to combine all of the files in a single folder and have the query figure out which delimiter each file has.
But the 2nd option is good also if I do not want to refresh 2 times instead of 1 (if I have I refresh for the main Folder, which will reaches out the 2 sub-folders) - because i have another case with files with 2 different columns , si i need to make some transformations.
Can you briefly tell me how to do it please?
Thanks xx
Sky571 - Sorry, one other question while I try to work through how to do this, is there any other way to identify the 2 sets of files other than which folder they are in and their delimiter? In other words, do they follow different file naming conventions or have different file extensions?
Hello Sky571
you could try to count for your limiter with a function like this, and integrate this in your query
= (Path, Limiter)=> let Source = Csv.Document(File.Contents(Path),null,null,null,null), CountLimiter = List.Sum(List.Transform(Source[Column1], each Text.PositionOfAny(_,{Limiter}))) in CountLimiter
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
JimmySky571 - 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.
Hello 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 CombineTableCopy 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
