Forum Discussion
How to automate Excel Files transformation?
Fernando,
I do this sort of thing using PowerQuery (PQ). You can either connect PQ directly to your data source or use the Load > From Table option to load your raw table into PQ.
From there, you can
- create delimited columns (to break out birthdate)
- create conditional columns to recode numeric values into text
- bring in another table and merge it with the original table to transform the values. For example, you can bring in a list of STATE abbreviations with the full state name. Merging tables this way saves you from having to write a bunch of nested ifs or long conditionals.
Each step you add in PQ is saved and automatically applied to new rows in the source upon refresh.
When you finish setting up your steps in PQ, you can load your transformed table to an Excel worksheet.
Fernando,
I do this sort of thing using PowerQuery (PQ). You can either connect PQ directly to your data source or use the Load > From Table option to load your raw table into PQ.
From there, you can
- create delimited columns (to break out birthdate)
- create conditional columns to recode numeric values into text
- bring in another table and merge it with the original table to transform the values. For example, you can bring in a list of STATE abbreviations with the full state name. Merging tables this way saves you from having to write a bunch of nested ifs or long conditionals.
Each step you add in PQ is saved and automatically applied to new rows in the source upon refresh.
When you finish setting up your steps in PQ, you can load your transformed table to an Excel worksheet.
chrisu, thank you very much for your info! This is really in the way of what I've been looking for!
One issue that I'm finding, and that's why i'm responding so late here, is to use the query I once created in other files.
Example:
I've opened the Excel 2016 as a blank file;
Started the Query Editor, loaded a file name "Test123" and transformed the whole thing;
Now the Query is attached to the "Test123" and this book1 file. I've saved the book1 as "defaultQuery";
The issue I'm finding is to use the "defaultQuery" on the "test123_v2" (test123_v2 is a new extraction from my database, with the same columns and order, but with new infos).
Do you happen to know how to do it? Or I must update the Test123 for the rest of the use of this query?
Thank you very much again!
Cheers,