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! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
76 | |
63 | |
51 | |
48 |
User | Count |
---|---|
204 | |
86 | |
64 | |
59 | |
56 |