Hi Junko,

That file has an empty query so I'm unable to help you.

Mynda

Hi Sorry for the wrong file attachment. This is the one I was referring to. One another question is if I delete columns would that be dynamically updated when the new file is updated? Because I get errors saying they cannot find the column I deleted. How do i get around that?

The other question still remains, as i cannot clear the 1st, I tried to copy and paste the "GET SOURCE" address for dynamically updating the files in the folder. Not sure if it is working.

Hi Junko,

The query and sheet names in this file do not correspond to your initial question above where you mention 'Transfer data 3' and 'Data 2'.

That said, this approach assumes the data in each file you're getting is in a consistent format so any transformations you need to do can be done in the final query once all data is appended.

I'm sorry, but I don't know what you mean by: "The other question still remains, as i cannot clear the 1st, I tried to copy and paste the "GET SOURCE" address for dynamically updating the files in the folder. Not sure if it is working."

Can you try rephrasing your question with specific reference to the sheets and queries so I can follow what the issues are?

Thanks,

Mynda

HI, I apologize for not being clear enough. Please forget the names I mentioned because I recreated new ones.

Here is the list of things I was aiming for:

1. To format the all the dates in the correct order "YYYY/MM/DD" (Original column names: Repair date, Registration date, Credit, Parts fitted, Delivery date) from one csv file from folder (which is named  "Data" table) 

----I was able to format by creating Add Column and using Text. Start and Text.Middle to extract the numbers and merge, recreated all data columns with new names ("Repairdate", "Registrationdate","Creditdate", "Partsfitteddate", "Deliverydate") (no space in field name to distinguish from original columns)

2. To format the field "Ref. no. - job no" (column 5)  to exclude the space after the hyphen 

----I used split column to separate and merge it into 1 column to get rid of space after hypen, renamed merged column to "Referencenumber" (column 4)

3. To insert the get FOLDER FILE PATH to update the csv files to add to the Data table

---This I tried to do, but for some reason after I insert the following:

let

    //Get Text or CSV files from a folder

    Path = Excel.CurrentWorkbook(){[Name="PathTable"]}[Content],

    Source = Folder.Files(Text.Combine({Path[FolderPathColumn]{0} ,"SourceFiles"},""))

in

    Source

---I get an error. ( I assume it is looking for the files I had deleted in Data table before I formatted them) 

4. To automatically update the csv files in the same format as Data table

---I was not able to accomplish because I got stuck in #3 with error.

Hi Junko,

I can't tell what the problem is without knowing what error you're getting. I don't have access to your source files so the error I get when I open the file won't be the same as the one you see.

That said, when I look at the M code for the query called 'Formatted' there is a commented out line of code called 'Source' and the file path in there does not contain a folder called 'SourceFiles', so it's likely that is the cause of the problem. The query is expecting to find your files in a folder called 'SourceFiles'.

Please go back through that lesson, paying particular attention to the purpose of the folder name 'SourceFiles'.

Mynda