I want to analyse some spectral data.
I have a ~6500 csv files.
Each .csv file contains data with the fromat shown in pics.
How can I transpose all csv files?? ....so then....I can combine them in powerQuery??
Thank you!
0
votes
SO isn't a code-writing service. This is a large question. You'll want to work with Power Query's pivot and unpivot transforms as well as 'Load from folder'. It may be beneficial to break this down into multiple steps. It is also useful to share what pieces of the problem you have solved (and show your code) and the pieces you've attempted but haven't solved (showing the code you tried and explaining what fails).
– greggyb
1 Answers
0
votes
This will read in all .csv files in a directory, transpose and combine them for you
Use Home...advanced editor... to paste into PowerQuery and edit the 2nd line with the appropriate directory path
Based on Alexis Olson recent answer Reading the first n rows of a csv without parsing the whole file in Power Query
let
Source = Folder.Files("C:\directory\subdirectory\"),
#"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".csv")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Content", "Name"}),
#"Invert" = Table.TransformColumns(#"Removed Other Columns", {{"Content", each Table.Transpose(Csv.Document(_))}}),
MaxColumns = List.Max(List.Transform(#"Invert"[Content], each Table.ColumnCount(_))),
#"Expanded Content" = Table.ExpandTableColumn(#"Invert", "Content", List.Transform({1..MaxColumns}, each "Column" & Number.ToText(_))),
#"Promoted Headers" = Table.PromoteHeaders(#"Expanded Content", [PromoteAllScalars=true]),
#"Filtered Rows1" = Table.SelectRows(#"Promoted Headers", each ([Date] <> "Date"))
in #"Filtered Rows1"
