cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sky571
Advocate I
Advocate I

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 :

 

Sky571_0-1598092340026.png

 

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

1 ACCEPTED SOLUTION

@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.

edhans_0-1598283505390.png

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.

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

22 REPLIES 22
Jimmy801
Super User III
Super User III

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

Jimmy

 

 

Hello @Jimmy801 ,

 

I don't fully understand what you are trying to do with counting the delimiter.

 

Can you show me a .pbix file with an example please?

 

Thank you a lot !

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
    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

 

 

Hello @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

 

 

Hello @Sky571 

 

i cannot follow you. 

In my test case one file had 4 columns the other one 8 and the result is one table with 8 columns were, but showing for the part of the 4 columns-table on the other fields only "null"

image.png

 

BR

Jimmy

@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.

edhans_0-1598283505390.png

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.

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

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.

 

 

 

edhans
Super User III
Super User III

Excellent @Sky571 glad you have your solution and that I was able to help.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Greg_Deckler
Super User IV
Super User IV

@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?


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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 - I don't quite have my example working yet, but here is what I am thinking.

 

Your main query in a Folder query basically looks like this:

let
    Source = Folder.Files("C:\temp\powerbi\csvs"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"column1", Int64.Type}, {"column2", Int64.Type}, {"column3", Int64.Type}, {"date", type date}})
in
    #"Changed Type"

 

So, you could do this:

let
    Source1 = Folder.Files("C:\temp\powerbi\csvs"),
    Source2 = Folder.Files("C:\temp\powerbi\csvs1"),
    Source = Table.Append(Source1,Source2),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"column1", Int64.Type}, {"column2", Int64.Type}, {"column3", Int64.Type}, {"date", type date}})
in
    #"Changed Type"

 

That part gets all of your files being fed through the same process. Next you have your Transform Sample File function that looks like this:

let
    Source = Csv.Document(Parameter1,[Delimiter=",", Columns=4, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
    #"Promoted Headers"

So what I am thinking is that you add a Parameter2 to the Helper Queries folder and you set that when you call the function. And you can tell what delimiter to set for this parameter because of what folder your file is coming from, so the Transform Sample File would end up looking like:

let
    Source = Csv.Document(Parameter1,[Delimiter=Parameter2, Columns=4, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
    #"Promoted Headers"

As I said, I don't quite have it all working just yet but figured I'd explain what I am thinking and maybe you can figure it all out before I can or maybe @ImkeF and @edhans or someone better at Power Query than I can step in and wrap it up. But, seems definitely possible to me.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@Greg_Deckler 

 

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 ! 🙂

Sorry @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 

@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.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@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.

edhans_0-1598135951699.png

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?

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

@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!


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@Greg_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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

@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.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hello @edhans and @Greg_Deckler ,

 

Thank you for searching.

 

Yes I do need a single code doing the transformation for both set of files. 

 

I did some tests and it does not work as I would though.

 

At the moment, I import 2 Folders with : 

  • Files (.txt) with " " delimiters
  • Files (.txt) with "I " delimiters

I would like to import 1 Folder only and say in parameters that it can handle both delimiters => do you think it is possible or I need to import 2 folders ?

 

Here is my sample files : https://drive.google.com/file/d/1OBcUymgK8flIg5IhCKX6eLN-02V-Y80o/view?usp=sharing 

 

Thank you for your time !

 

@Sky571 I am still working on this but my Power Query fu is not nearly as strong as someone like @edhans . I was going down the 2 folder route because it didn't seem like there was a way to identify which type of delimiter the file had other than what folder it is in.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors