I am looking for assistance on this project of mine. I want to be able to create a dynamic table that can be update based on the number of inputs.

The goal is to create a fully built out table that follows the tier structure. I have attached an example of what I am trying to do with the inputs above and intended output below and I want the table to be updated if I were to add a new selection to each tier e.g. D to tier 1 or 3 to tier 2

For each table, create a query using Data>Get & Transform Data>From Table/Range.

You should then have three queries in the Power Query Editor, like this:

In the Power Query Editor, select the "tier1" query, then use Add Column>Custom Column and configure it like this:

When you hit OK, you will see this:

Hit the double-headed arrow at the top of the new column then hit OK on the dialog to expand the column. You'll see this at the end:

Repeat the above steps for adding a column for tier3, so at the end you have this:

You can now right-click any of the columns and use 'Rename' to rename them as you want.

Finally, click 'Close & Load' to put the result back to the workbook.

Now, you only need to put your tier values into the three tables, then right-click the final query and select 'Refresh' to run the steps again.