ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now

Alteryx Analytics Hub

Find answers, ask questions, and share expertise about Alteryx Analytics Hub.

How to create a complicated spreadsheet by using exisitng data

5 - Atom

Greeting everyone,


I've tried to create a work flow to generate a complicated spreadsheet by using existing data. However, I couldn't work out the logic and struggle to use the right method to solve this issue.

I have a original data.xlsx

I need to generate a spreadsheet as template.xlsx

Thus, I need to create new columns of "Bus#", "kV", "Load ID", "District" and "each year's % of CityA Total MW" in original data. For Load ID, I need to leave those Subs without IDs as blank.

For each year's MW value, I need to import the values from original data spreadsheet and also divide each value by 1000. For Sub 4, 5, 6 and 7, I need to add their MW value with Sub 3 until 2021. After 2021, I only need to add Sub 4, 5 and 7 with Sub 3's MW value, Sub 6 leaves it as its own.

For each year's percentage value, I need to calculate a Total MW value of all the subs first for each year (create a new row to generate this value) and then use each sub's MW value divide by Total MW value to get percentage value.


Any suggestions or advise that I could create the work flow? Thanks for the help in advance.




14 - Magnetar

Hi @esun1028 ,


I've developed a workflow that gets you most of the way there.


Here's a quick run-through:


Step 1 - Read in the data and structure it in a clean way we can work with


Step 2 - Calculate MW and % of Total MW for each substation and year


Step 3 - Apply business rules for substations 4,5,6 and 7


There are a few tools in here that are particularly helpful for this use case, and I'd recommend the Tool Mastery sessions below to get more comfortable with them:


Transpose -

Cross Tab -

Dynamic Rename -


Hopefully this gets you close to the output you need. For your final steps of writing to the formatted Excel template, check out the article below: