How to automate Excel Files transformation?

[TernandoFoledo]

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 to the table that I've developed the report to work on. To clarify, this is what the table looked like and what it looks now:

Raw

Transformed

So, I'm looking for something to: automate this process of dividing the "birthdate" into some collumns, transform the status from numbers to words (like, 1=Cancelled - I know I can make some formula with =if or things like that, but I wanna know how to make this available to every new data upload), and things like that.

Is there any light? 🙂

Thank you very much!!

Best,

Fernando

[CahabaData]

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....

[chrisu · Accepted solution]

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

1. create delimited columns (to break out birthdate)
2. create conditional columns to recode numeric values into text
3. 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.

[TernandoFoledo]

@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,