How to create a complicated spreadsheet by using exisitng data

[esun1028]

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.

[jamielaird]

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 - https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-Transpose/ta-p/89741

Cross Tab - https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-Cross-Tab/ta-p/4368

Dynamic Rename - https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-Dynamic-Rename/ta-p/32...

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:

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/How-do-I-output-to-an-Excel-templat...