Data massage for survey export - Gender

[carlG]

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

[amitchandak]

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

[carlG]

@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

[Greg_Deckler]

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

[carlG]

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.

[Greg_Deckler · Accepted 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]

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.

[Greg_Deckler]

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