Dec 08 2021 11:24 AM
Hello,
I have a requirement where i need to populate the entries based on an input table.
The input table below has 2 columns, VM Type and # of VMs, basically it tells how many VMs are there for each of the VM Type and this number can change for our different deployments.
My requirement is to use this as an input and populate all the VM names for all the VM Types. The example output shows the expected result, for example for VM Type3, the input table says it should have 12 VMs and the output shows the VM names from 001 to 013.
also, if the input data changes. for example, the number of VMs for Type3 changes from 12 to 15, the output table should reflect the increase in VMs from 12 to 15 dynamically.
Input Table
VM Type | # of VMs |
Type1 | 2 |
Type2 | 5 |
Type3 | 12 |
Type4 | 6 |
Expected Output Table
VM Name |
Type1001 |
Type1002 |
Type2001 |
Type2002 |
Type2003 |
Type2004 |
Type2005 |
Type3001 |
Type3002 |
Type3003 |
Type3004 |
Type3005 |
Type3006 |
Type3007 |
Type3008 |
Type3009 |
Type3010 |
Type3011 |
Type3012 |
Type4001 |
Type4002 |
Type4003 |
Type4004 |
Type4005 |
Type4006 |
please let me know if any other information is needed.
Thank you guys in advance.
Dec 08 2021 11:01 PM
@Vijay_Uppala I'd suggest you look into Power Query, as demonstrated in the attached file. Connect to the Input Table with PQ, do several transformation and load the end result back to Excel as illustrated in the picture below. Add rows to the (structured) blue table. Press "Refresh All" on the Data ribbon and the green table (=PQ output) will update instantly.
Dec 09 2021 09:56 AM
@Riny_van_EekelenThank you very much for the solution.
Unfortunately, I should have mentioned that I'm working on MAC and I dont believe Power BI is supported on MAC.
Can you think of any other solution?
Thank You
Dec 09 2021 02:20 PM
SolutionI have added a couple of dynamic array solutions to @Riny_van_Eekelen 's Power Query solution. The second uses Lambda functions for the accumulation and most likely is not available to Vijay yet.
Dec 11 2021 05:25 PM
The dynamic array solution works really well.
I couldn't get the Lambda function to work, but, as you stated, may be it is not available on Mac.
Thank you very much for all your help.
Dec 09 2021 02:20 PM
SolutionI have added a couple of dynamic array solutions to @Riny_van_Eekelen 's Power Query solution. The second uses Lambda functions for the accumulation and most likely is not available to Vijay yet.