Search code examples
excelpowerquerytranspose

How to transpose multiple .txt files separately and combine them into 1 csv file

I'm working with hundreds of .txt files and I have to combine them into 1 single .csv file

text file format

Above is a sample format of the text file (there are only 2 columns but have hundreds more rows)

I'm required to first transpose the contents of each .txt file, and then merge all the results into one table, where they all have a common header row (the column of 31330_at, 31385_at, 31463_s_at etc)

This is my first time working with power query and I'm not entirely sure how to do this as I've tried importing all files and transposing them all at once, but it doesn't work.

let
   Source = Folder.Files("Directory),
   #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".TXT")),
   #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Content", "Name"}),
   #"Invert" = Table.TransformColumns(#"Removed Other Columns", {{"Content", eachTable.Transpose(_)}}),
.....

I've tried the code above but it runs into an error Expression.Error: We cannot convert a value of type Binary to type Table. at the #'Invert' function

For reference, it's the same concept as this link https://stackguides.com/questions/57805673/how-to-transpose-multiple-csv-files-and-combine-in-excel-power-query

How do I fix this?

Solution

  • It looks like you already know how to pull the files from the folder, as shown by your Source = Folder.Files("Directory).

    After combining the Binary files, you probably see something like this on your screen, where all of the txt files are appended one after the other:

    enter image description here

    But that's not what you want. Right? You want the files appended based upon a transposed view of each file. I understand from your description above, that the first column of each file will contain the same information as the first column of every other file, and you want that first column's information to be used as the header for the information that is initially listed in column 2 of each file but will be transposed into appended rows.)

    It looks like you are trying to do your transposing in the query that is generated and listed under Other Queries (probably called Directory for you).

    enter image description here

    Don't do the transposing there. Instead, look for the query called Transform Sample File, which should be listed under Helper Queries, and do the transposing in it.

    enter image description here

    Click on the query named Transform Sample File.

    enter image description here

    Then click Transform -> Transpose, to transpose your table.

    enter image description here

    Then click the ribbon button for Use First Row as Headers, to make the first row your headers.

    enter image description here

    Then click on that earlier query that is listed under Other Queries (probably called Directory for you)

    enter image description here

    ...and you will see this error message:

    enter image description here

    This error is caused because the final step of the query is trying to change types using the old column names. So look to the right side of your screen and delete the Changed Type step by clicking on the X before Changed Type. (If you need to, you can change column types later, for the columns that need it.)

    enter image description here

    Then you should see what I understand you are wanting to see as a result.

    enter image description here