Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Struggling to find a way to manipulate survey data into a single column.
Currently there are 76 different columns in this export, and trying to reduce where possible.
Question: is there a way to combine the gender (Col11-14) into a single column? Bonus for not using DAX if possible.
Snapshot of table
thank you in advance for any tips
Solved! Go to Solution.
@carlG - Try this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs9ILFHILFaozC8tUkhPzUtJLVLSUVKA41idaCW31NzEnFQg1xdC+ZdkgFUFJYIYCnn5JQrFiZVgpS6pufnpRYkFGZnJGMYo4BUxBIsYYlVjiCKCqQYkAkWxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([#"(blank)"] <> "Demographic" and [#"(blank)"] <> "What is your gender")),
#"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Female", type text}, {"Male", type text}, {"Other", type text}, {"Rather not say", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type1","1","Female",Replacer.ReplaceText,{"Female"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","1","Male",Replacer.ReplaceText,{"Male"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","1","Other",Replacer.ReplaceText,{"Other"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","1","Rather not say",Replacer.ReplaceText,{"Rather not say"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Replaced Value3", {}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
#"Filtered Rows1" = Table.SelectRows(#"Removed Columns", each ([Value] <> "" and [Value] <> " ")),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows1",{{"Value", "What is your gender"}})
in
#"Renamed Columns"
@carlG , Unpivot
https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/
After in pivot you ca group data to get one row - https://www.poweredsolutions.co/2019/07/30/grouping-rows-with-power-bi-power-query/
@amitchandakThank you for the suggestion,
Unfortunately when I unpivot, I am left with gaps in my data, and the grouping by rows does not seem to retain the other data
Unpivoted data sample
Participant Unique ID | What is your gender |
402439 | |
402439 | |
402439 | |
402439 | |
419083 | |
419083 | |
419083 | |
419083 | Rather not say |
406325 | Female |
406325 | |
406325 | |
406325 | |
406329 | |
406329 | Male |
406329 | |
406329 | |
406332 | Female |
406332 | |
406332 | |
406332 | |
406334 | Female |
@carlG - Well, the obvious thing would be unpivot those four columns but I feel like you've supplied like 10% of a problem. Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
Apologies @Greg_Deckler good suggestion, attempt 2 below.
Issue with combining data.
Sample Table Data as text:
What is your gender | |||
Female | Male | Other | Rather not say |
Demographic | |||
1 | |||
1 | |||
1 | |||
1 | |||
1 |
Expected Outcome
What is your gender |
Rather not say |
Female |
Male |
Female |
Female |
Notes:
dataset expected to hit 10K+ records by end of each year (My high school students complete survey at end of every class)
This issue exists multiple times throughout the 76 column CSV export.
@carlG - Try this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs9ILFHILFaozC8tUkhPzUtJLVLSUVKA41idaCW31NzEnFQg1xdC+ZdkgFUFJYIYCnn5JQrFiZVgpS6pufnpRYkFGZnJGMYo4BUxBIsYYlVjiCKCqQYkAkWxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([#"(blank)"] <> "Demographic" and [#"(blank)"] <> "What is your gender")),
#"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Female", type text}, {"Male", type text}, {"Other", type text}, {"Rather not say", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type1","1","Female",Replacer.ReplaceText,{"Female"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","1","Male",Replacer.ReplaceText,{"Male"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","1","Other",Replacer.ReplaceText,{"Other"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","1","Rather not say",Replacer.ReplaceText,{"Rather not say"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Replaced Value3", {}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
#"Filtered Rows1" = Table.SelectRows(#"Removed Columns", each ([Value] <> "" and [Value] <> " ")),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows1",{{"Value", "What is your gender"}})
in
#"Renamed Columns"
Awesome @Greg_Deckler , I now know how @amitchandak intended.
How would I handle a field that has no data in it?
i.e. Top survey participant has been removed from the dataset as they decided not to answer the gender question. (Not allowed to make a required survey question) - not a blank row.
@carlG - That is up to you. Again, I feel like I am being given about 10% of the required information to a much larger problem. You could create a fifth column "No Reponse" that would check if all four other columns are null and if so return "No Reponse" for that row, otherwise it would be null. Then you would unpivot those 5 columns instead of 4?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |