Import lots of txt files into separate rows in one worksheet

[MP_7]

Hello,

I'm not good at VBA and neither at power gueries, but I'm trying with the latter, and I'm having some troubles.
What I have is a lot of txt files and the data in those files is just row after row, part of it below
2
3
144
2.50
73
2.50
OR4_GL02_300_3K_2A
etc...

I just need what each row contains into separate columns all in one row

for example:
file 1
A
B
C

file 2
D
E
F

Result:

A	B	C
D	E	F

I defined in the query to import only the first 40 rows but after I load it literally imports those 40 values into one row. I don't know how to import the rest of the files?

Thx.

 

[MP_7]

No problem, no worries, I appreciate you're taking the time to help me out, it's no hurry

 

[sandy666]

here is

s666-PQ-FromFolderTransposeTXT.xlsx

Microsoft Excel Workbook

1drv.ms

change path

 

[MP_7]

Holy crap!!! Thx Sandy I can't thank you enough, during the weekend I will study your steps, this is the first time I'm using power queries and I can see myself using it in the future, this is awesome!
Thank you so much for your time and patience!

 

[sandy666]

Welcome to mysterious Power Query Zone

for future

Power Query documentation - Power Query

Power Query is used to filter, combine, and mash up data from one or more supported data sources.

docs.microsoft.com

btw. half of steps are automated from feature From Folder (red frame in the picture - post#24)
you can modify first line: Source = Table.Buffer(Folder.Files("D:\test\mp_7\txt")),
maybe it will speed up a little (of course after change path)

 

[sandy666]

try with this
i added Table.Buffer and primary key so it should be faster

let
    Source = Table.Buffer(Folder.Files("D:\test\mp_7\txt")), //change path
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from txt", each #"Transform File from txt"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from txt"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from txt", Table.ColumnNames(#"Transform File from txt"(#"Sample File"))),
    TAK = Table.AddKey(#"Expanded Table Column1", {"Source.Name"}, true),
    #"Added Conditional Column" = Table.AddColumn(TAK, "Custom", each if Text.Contains([Column1], "GMR Enlights") then [Column1] else null),
    #"Grouped Rows" = Table.Group(#"Added Conditional Column", {"Source.Name", "Custom"}, {{"Count", each _, type table}, {"CR", each Table.RowCount(_), type number}}),
    #"Filled Down" = Table.FillDown(#"Grouped Rows",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each [CR] <> 1),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "List", each [Count][Column1]),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"List", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "List", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"List.1", "List.2", "List.3", "List.4", "List.5", "List.6", "List.7", "List.8", "List.9", "List.10", "List.11", "List.12", "List.13", "List.14", "List.15", "List.16", "List.17", "List.18", "List.19", "List.20", "List.21", "List.22", "List.23", "List.24", "List.25", "List.26", "List.27", "List.28", "List.29", "List.30", "List.31", "List.32"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Source.Name", "CR"})
in
    #"Removed Columns"

 

[MP_7]

Wow, thx, will try it when I come home

u 2 mate !

 

[sandy666]

sure, have a nice day

 

[sandy666]

a bit shorter (and optimised, I hope )

let
    Source = Folder.Files("D:\test\mp_7\txt"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from txt", each #"Transform File from txt"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from txt"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from txt", Table.ColumnNames(#"Transform File from txt"(#"Sample File"))),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded Table Column1", "Label", each if Text.Contains([Column1], "GMR Enlights") then [Column1] else "GMR Enlights"),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Column1] <> "GMR Enlights")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Source.Name", "Label"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "List", each List.FirstN([Count][Column1],32)),
    #"Extracted Values" = Table.Buffer(Table.TransformColumns(#"Added Custom", {"List", each Text.Combine(List.Transform(_, Text.From), ","), type text})),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "List", Splitter.SplitTextByAnyDelimiter({","}, QuoteStyle.Csv),32),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Source.Name", "Count"})
in
    #"Removed Columns"

 

[MP_7]

Wow, it really is faster! Thank you so much!

 

[sandy666]

You are welcome & thanks for the feedback
Have a nice day