Solved: How to combine Folders - Page 2

Sky571 · ‎08-22-2020

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

edhans · ‎08-24-2020

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

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

Greg_Deckler · ‎08-22-2020

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Sky571 · ‎08-22-2020

@Greg_Deckler ,

Thank for your question.

The files have the same extensions (.txt)

For their names : they begin by the same first letter as FEC, but after it changes according to the beginning and ending of a fiscal period :

example: FEC - 01-01-2015 - 31-12-2015 => they have between 18 columns and 22, with the same order and name header, except for 1 case (where i have 2 columns with different names+content, so i need to transform those in order to harmonize with my first example file)

So,

What is different is :

1- Delimiters : either " " , or " I "

2- Name and content of 2 columns, which requires transformation queries

Hope this explanation will help !

How to combine Folders

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024