Convert One Row of Data into Multiple Rows

Tallyski 
There is a solution in Power Query, but it a few steps.

1. Select the 4  "Key components" columns and unpivot via the Transform tab.
2. Select the 2 "States"Columns and unpivot them as well.

3. Remove the attribute columns and

4. rename the value column 
Now you have a table but all the records are double.

5.Then add a custom column via the add column tab.

= if [Select your top two key components for a successful team] <> "" then null else [Value.1])

Name this new column "Name 2 states you lived in".
6. Remove the values.1 column
7. Remove the duplicates.

The full M code here is

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIrSk0BUs75ubmleZnJiSWZ+XlAfnBJUX5euoJPamJKalFxRmYBUAyCQiKARICjUqxOtJIRkOmbWFQJkcGhxzE5Ob80ryQxKTMnswSk1NkfSPh7K8XGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"id #" = _t, name = _t, #"Select your top two key components for a successful team" = _t, #"Select your top two key components for a successful team.1" = _t, #"Select your top two key components for a successful team.2" = _t, #"Select your top two key components for a successful team.3" = _t, #"Name two states you have lived in" = _t, #"Name two states you have lived in.1" = _t]),
    Unpivoted1 = Table.UnpivotOtherColumns(Source, {"id #", "name", "Name two states you have lived in", "Name two states you have lived in.1"}, "Attribute", "Value"),
    Unpivoted2 = Table.UnpivotOtherColumns(Unpivoted1, {"id #", "name", "Attribute", "Value"}, "Attribute.1", "Value.1"),
    Remove_attribut = Table.SelectColumns(Unpivoted2,{"id #", "name", "Value", "Value.1" }),
    Rename_value = Table.RenameColumns(Remove_attribut,{{"Value", "Select your top two key components for a successful team"}}),
    AddCustom = Table.AddColumn(Rename_value, "Name two states you have lived in", each if [Select your top two key components for a successful team] <> "" then null else [Value.1]),
    Remove_value1 = Table.RemoveColumns(AddCustom,{"Value.1"}),
    Removed_Duplicates = Table.Distinct(Remove_value1)
in
    Removed_Duplicates

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

Best regards,
Jeroen

Tallyski 
Based on your question it did not realize it were that many groups and column. Not sure if that will work. The formula's will change but I doubt this is- the best solution on this data size.

You could consider making 4 different query's one for each group. Unpivot that group.
Then Append All the groups into one query.

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

Best regards,
Jeroen

Hi Tallyski ,

You can refer to the method in my reply in this topic.

https://community.powerbi.com/t5/Desktop/plot-graphs-with-multiple-dates/m-p/2224757#M811861 

Best Regards,

Jay