Forum Discussion
Solved
How to automate Excel Files transformation?
Greetings everyone! I'm new here at the community and I do have some doubts that I couldn't find the answers on search. I'm looking for something to automate my data to fit from the raw table...
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.
in Power BI, using the Query Editor to modify starting tables - this remains applied to that table (excel sheet) when its data is refreshed. there is nothing explicitly needed in terms of automation to re-do those steps assuming your data refresh is the same starting point of the same excel file names/ column names.....just new data....