Combine TXT files based on multiple deliminators

[RipeKiwi]

Hey all!

I am looking to combine these files Drive to folder/files from a folder level. The issue is that when combining i can only select 1 deliminator, either comma or semi-colon. If it was just these 2 files I would change the deliminator in one of the files, there are numerous files and i would prefer not to edit them (even if a script can do it all for me!).

Any thoughts?

Thanks in advance!

[Mahesh0016]

@RipeKiwiPlease Can you Share your EndOutPut for better understanding? Thank You!!

[RipeKiwi]

@Mahesh0016 thank you for asking, but I am not sure what EndOutput you are referring to.

Below is my code:

let
    Source = Folder.Files("C:\Users\qq\Desktop\PQ 1"),
    #"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"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Renamed Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}, {"Column24", type text}, {"Column25", type text}, {"Column26", type text}, {"Column27", type text}, {"Column28", type text}, {"Column29", type text}, {"Column30", type text}, {"Column31", type text}, {"Column32", type text}})
in
    #"Changed Type"

First 14 rows and 2 columns of my table:

Folder PathColumn1 C:\Users\PQ 1\20200206\ Count                       C:\Users\PQ 1\20200206\ --------------------------- C:\Users\PQ 1\20200206\ . 013ff5779                 C:\Users\PQ 1\20200206\ . 0132GW489                 C:\Users\PQ 1\20200206\ . 0Q3215409                 C:\Users\PQ 1\20200206\ . 013215409                 C:\Users\PQ 1\20200206\ . Q13215419                 C:\Users\PQ 1\20200206\ . X13215439                 C:\Users\PQ 1\20200206\ . A13219409                 C:\Users\PQ 1\20200206\   C:\Users\PQ 1\20200824\ Count                      ;Internal Sec No            ;SINE      ;Type        ;Quantity         ;Amount           ;Status;CP No ;COOP Clear           ;Finder;Client No;Rate     ;Rate Code;PC;Coll Code;Mark Type;Mark Amount;Div F/T Rate;Asset Account;Fixed;No Tracking;Settlement Date;Expiration Date;Comments              ;Contract No;Depo            ;SOD     ;Account                    ;Db/Cr;Deal Info;CP Name                                                      ;Effective C:\Users\PQ 1\20200824\ ---------------------------;---------------------------;------------;------------;-----------------;-----------------;------;------;----------------------;------;----------------;---------;---------;-------------;---------;---------;-----------;------------;-------------;-----------------;-----------;---------------;---------------;----------------------;-----------;----------------------;-----------------;---------------------------;-----;---------;-------------------------------------------------------------;--------------------- C:\Users\PQ 1\20200824\ . 011234509                ;.                          ;. GGGG      ;. S/B       ;        9200.0000;         18400.00;. OPE ;. 3121;. 00001235            ;. 0000;. 1198          ;  95.0000;. N      ;. M          ;. C      ;.        ;          0;    100.0000;.            ;            .0000;.          ;2020/08/19     ;               ;.                  ZZZ;. 402325007;. QTC                 ;        9200.0000;. 97APLEB00                ;. D  ;         ;'PPL13                                        ;             -95.0000