Updating CSV data

[gavinhuet]

Hi, If I have data in a csv file it imports nicely. ColumnA,ColumnB "R1C1","R1C2" The first issue is that if I swap the columns around and get data again I am prompted to override the existing the dataset, the columns are ignored and the data is imported as though it is the original file - no warnings. ColumnB,ColumnA "R1C2","R1C1" "R2C2","R2C1" Imports as ColumnA,ColumnB "R1C2","R1C1" "R2C2","R2C1" The second issue is that if I then add ColumnC, Get Data again, I am once again prompted to override the existing dataset. This time however it says that there is an error and the data is then lost. Surely at that point you should be given the option to undo the change? Kind regards Gavin See http://community.powerbi.com/t5/Service/Issues-with-CSV-files/m-p/62477

[gavinhuet]

No text was lost, I can see all the text. Not sure it can be classed as "working as expected". Surely the import should check the file header for changes, not just the file name. And if there are any differences it should allow the option to continue or cancel. A change such as an additional column being added to the end of the file should not cause this kind of issue, over time most datasets would have more columns added.

[gavinhuet]

Hi Charles, Thanks for your help and the explanation. The Desktop version it is then... Regards Gavin

[chass]

Hello Gavin, The reason i say it is probably working correctly is the same reason the advanced editor steps were requested in your forum post-which is to see what steps are being applied. If you look at the desktop version of this process, you will see the steps look something like those below. Those steps assume two columns (see the source line) and the first row is assumed to be the headers. When you add a column the system doesn't know if the CSV was corrupt or this was an intentional change. Adding columns to the same dataset can be supported in the M language and the Desktop version...In that scenario you are deciding how to handle the data change scenario. (like the suggestion in the forum) Does that make sense? let Source = Csv.Document(File.Contents("C:\Users\chass\Documents\one.csv"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Change Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}), #"Promoted Headers" = Table.PromoteHeaders(#"Change Type") in #"Promoted Headers" Here is an example of people using M to modify/control the data import: https://social.technet.microsoft.com/Forums/en-US/1e40ed70-3c61-4829-ba38-85bb2e9438b0/promote-multiple-rows-to-header?forum=powerquery