Convert One Row of Data into Multiple Rows

[Tallyski]

Hello, 

I've been working with Power Bi for a few months now; I have used the forums to find answers to my questions. Unfortunately, I have to make this post as I haven't been able to find a solution.

I am trying to convert data in the first table, to look like the data in the second table. Is it possible in Power BI?

id #	name	Select your top two key components for a successful team	Select your top two key components for a successful team	Select your top two key components for a successful team	Select your top two key components for a successful team	Name two states you have lived in	Name two states you have lived in
1	Fred	Communication	Strong Leadership			TX	PA
2	Mary		Strong Leadership		Accountability	CO	OK

id #	name	Select your top two key components for a successful team	Name two States you have lived in
1	Fred	Communication
1	Fred	Strong Leadership
1	Fred		TX
1	Fred		PA
2	Mary	Strong Leadership
2	Mary	Accountability
2	Mary		CO
2	Mary		OK

I have used DAX quite a bit, but unsure how or if this is possible. I have a much larger table of data to work with, but this is just a sample of the problem I am trying to solve.

Thanks in advance.

[v-jayw-msft]

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

[jeroendekk]

@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

[jeroendekk]

@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]

Jeroen,

Thank you for you guidance on my request, but I feel I am doing something wrong.

I start with:

601 rows

78 columns

I need to unpivot 4 groups

1st = 13 columns

2nd = 14 columns

3rd = 6 columns

4th = 23 columns

Once I unpivot them I have over 15 million rows. I try to do the second part, but it doesn't delete and rows. I'm not quite sure what I am doing wrong.

I would appreciate any guidance.

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

[Tallyski]

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