Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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.
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
@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
@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
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])
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
104 | |
75 | |
43 | |
39 | |
32 |
User | Count |
---|---|
170 | |
90 | |
65 | |
46 | |
44 |