Text files

[Matthiaskirsch]

Dear all,

being an absolute greehorn, I would appreciate getting instruction how to use Powerquery and the associateeditor for the following problem:

I have a folder with .txt- files, each consisting of a single column of data without header. These should be combined into a single excel worksheet with names of files as column headers, each column containing data from a single file?

So far I have only managed to combine the files yielding a single colum of data: the first column contain the name of the txt-file repeated as often as there are data rows in the file.

However, I want the content of the single data column of each file in separate colums in the resulting worksheet (only one worksheet with as many columns as there are txt-files.

Ideally this worksheet should get updated each time I add a new txt-file to the source folder.

 

Hope I made the problem clear enough for anybody out there to provide a working solution.

Thanks in advance and best,

Matthias

 

 

 

 

Von Samsung-Tablet gesendet

[wdx223_Daniel]

let

source=Folder.Files(YourPath),

custom1=Table.FromColumns(List.Transform(source[Content],each "your code to import text files"),source[Name])

in

custom1

[Matthiaskirsch]

So I just learned, that it is you, whom I have to thank for the working code. THANKX,

Matthias

Just made a mistake and replied to myself, which of course is stupid.

[Matthiaskirsch]

Hi Daniel,

thanks!

However, this does not seem to be the whole code.

I cannot figure out what you mean with: "your code to import text files"

As I said, I am completely new to PQ and really would appreciate a complete working solution. I may learn along the way, but for now, I have no clue (and little time)  apart from what I learn from tutorial videos and I have not found one, which matches my problem.

I am using Excel2016 by the way.

Best,

Matthias

[Matthiaskirsch]

So I just learned, that it is you, whom I have to thank for the working code. THANKX,

Matthias

[AlienSx]

Hello, @Matthiaskirsch . Try to replace "your code to import text files" with Lines.FromBinary(_) in the code provided by @wdx223_Daniel 

[Matthiaskirsch]

Hi AlienSx,

I guess I am just to dump. So this is what I see in the PQ-Editor:

let
Quelle = Folder.Files("C:\Users\kirsch\Desktop\text"),
custom1=Table.FromColumns(List.Transform(Quelle[Content],each "Lines.FromBinary(_) "),Quelle[Name])
in
custom1

Then I get the following error:

Expression.Error: Der Wert ""Lines.FromBinary(_) "" kann nicht in den Typ "List" konvertiert werden.
Details:
Value=Lines.FromBinary(_)
Type=Type

???

Best,

Matthias

[AlienSx · Accepted solution]

let
  Quelle = Folder.Files("C:\Users\kirsch\Desktop\text"),
  custom1 = Table.FromColumns(List.Transform(Quelle[Content], each Lines.FromBinary(_)),Quelle[Name])
in
  custom1

[Matthiaskirsch]

Dear AlienSx,

you are a genius. So simple and it works like a charm.

Thank you so much, this will help me a lot and my generate some more questions in the future, now that I have seen that I get useful answers this quick.

Best,

Matthias

[AlienSx]

it's @wdx223_Daniel who provided a solution to be honest. But thank you.