Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Tallyski
Advocate I
Advocate I

Convert One Row of Data into Multiple Rows

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 #nameSelect your top two key components for a successful teamSelect your top two key components for a successful teamSelect your top two key components for a successful teamSelect your top two key components for a successful teamName two states you have lived inName two states you have lived in
1FredCommunicationStrong Leadership  TXPA
2Mary Strong Leadership AccountabilityCOOK

 

id #nameSelect your top two key components for a successful teamName two States you have lived in
1FredCommunication 
1FredStrong Leadership 
1Fred TX
1Fred PA
2MaryStrong Leadership 
2MaryAccountability 
2Mary CO
2Mary 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.

 

4 REPLIES 4
v-jayw-msft
Community Support
Community Support

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
jeroendekk
Resolver V
Resolver V

@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
Resolver V
Resolver V

@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.
image 1.jpg
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.

image2.jpg

 

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

 

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric Community.