I would like to have a way to utilize PowerQuery/M to transform data from within MS Access. This would expand the use of Access beyond the existing connection options. I wouldn't mind if I had to use Excel or PowerBI to build the scripts using the Power Query Editor. Once the M script is created I'd like to be able to invoke it from Access and return the result as a dataset.
Microsoft, when considering a solution, handling case sensitivity of table data may need to be configured differently than table schema. In my case I had a column heading named "Task" that was renamed to "TASK". This broke a lot of steps. I expected that field names would be treated case insensitive the same way as in SQL.
While this issue seems to be about making column re-ordering easier in the UI, it would also be nice if the Table.ReorderColumns command had a way to move a column to the first or last without naming all of the other columns. In my scenario I have a pivot table with a variable number of columns and then I am adding a column to it. I want the new column to be somewhere on the left hand side of the table before all of the pivot columns. The ReorderColumns command won't consistently move the column to the new column where I want it unless I name all of the pivot columns (that have variable names). Commands like MoveFirst, MoveLast, MoveBefore, MoveAfter, would be helpful.