if you look at my attached zip folder called "new folder", these are multiple files i want to consolidate. i use the "combine & upload to" button. the result appear in another workbook "book1.xlsx" in attachment below. however, i am not satisfy with the formats as the first four rows of each source file "GSTIN", "legal name", "from" and "to" were shown in the "book1" as rows.
Is there a possible to convert them into columns (4 columns in total for GSTIN", "legal name", "from" and "to")?
kindly advice 🙂
No attachment!
apologize. please find my attachments below.
Hi Tan,
Please re-watch session 2.05 Get files from a folder. Here I explain that when you need to make changes prior to appending the data, that this must be done in the 'Transform Sample File' query. i.e. in that query you tidy up the header rows you don't want. This is then applied to all files prior to appending. You can simply select the Transform sample file query and make the changes to one file. Then select the 'New Folder' query to see the changes applied to all files.
Mynda
sorry Mynda...don't mind if you could work out 1 sample over here...
i tried to transpose first 4 rows GSTIN", "legal name", "from" and "to" into last 4 columns, next to column 16 "total" via transform sample file query
however, it fail...all rows & columns have been transposed at the same time...
Hi Tan,
You can't partially transpose a table, it's all or nothing. Please see my solution attached which requires adding 4 new columns to capture the data in column 5 on rows 3 to 6 of the sample query. Then transposing.
You will need to replace the folder path with your own via the advanced editor for the Sample file, and New Folder queries.
Mynda
super fantastic! i manage to work out by my own by opening the new workbook & new query, by referring to your applied steps in power query 🙂
please allow me to ask 1 more question. my original folder consists of all csv. files (these source data were received by different dept user). however on certain month, he provided me with xlsx. file (same content structure), instead of csv. files. when i use the same query to refresh, it seems fail to capture "xlsx" file, despite the contents are same.
any better way i could do, instead of manual save the xlsx file into csv file, before refresh again?
The function for getting CSV files is different to Excel files so you can't mix and match. You'd have to duplicate the query and change the function for getting Excel files, then append the two queries together.
If it's a one off, it'd be easier just to rename the files with .csv and be done with it.
