3 Comments
It would be nice if there was a way to edit data within PowerQuery.
Sometimes, you have raw data in a text file that is massive. Finding that data (in a text editor) and editing it in the source would be difficult and error prone.
Power Query should have a way to change data (a wizard of sorts would be nice).
The way I imagine it working is that the wizard helps you select which rows to edit and then which values to change.
In the background, the wizard writes M code that is suitable for the language and inserts it as a step. eg.
1) assigns a sequential id for each row,
2) assigns a hash for each row
2) manually defines a new table with matching row hashes and edited data (the whole row not just changed cells). Here it would probably be best to save the data in an external file that is loaded when the query runs, to avoid having the m code littered with data.
3) deletes offending rows from original
4) merges new rows
5) sorts by id
6) removes temporary sequential id and hashes.
^ this way, whenever the data is refreshed from the source, *if* the offending row still exists as it did when the edit was defined, it gets "re-edited" just as the user intended.
If I cant do what I need to within power bi, I will just use some other software. If I am already using another software, I will just write my reports there. I am not daisy chaining BI tools.
- Status added:Needs Votes
