Forum Discussion
remove duplicates in columns after unpivoting
Hello,
I have a survey result from excel that I imported into power query, the excel table is currently in a wide format, for the purposes of my visualizations and analysis, I was able to turn the wide table into a long table using unpivot. This is what the table used ot look like:
| ResponseID | Are you familiar with the Cubic Program? | How would you rate it's ease of use | If you chose unsure for easy of use, please explain | How would you rate how easily it can be acquired? | If you chose unsure for ease of acquiring, please explain | Are you familiar with the Boltic program? | How would you rate it's ease of use | If you chose unsure for easy of use, please explain | How would you rate how easily it can be acquired? | If you chose unsure for ease of acquiring, please explain |
| 1 | Yes | low | N/a | med | N/a | No | N/a | N/a | N/a | N/a |
| 2 | No | N/a | N/a | N/a | N/a | Yes | high | N/a | high | N/a |
after unpivoting, I have come across duplicate issues that looks like this, and I cannot solve the issue to get the numbers to be unique. this is how the duplicates are showing up:
| Uniq ID | ResponseID | Program | Program Response | Ease of Use | Ease of Use Response |
| Cubic 1 | 1 | Cubic | Yes | How would you rate it's ease of use? | N/a |
| Cubic 1 | 1 | Cubic | Yes | How would you rate it's ease of use?2 | low |
| Cubic 1 | 1 | Cubic | Yes | How would you rate it's ease of use?3 | med |
| Cubic 1 | 1 | Cubic | Yes | How would you rate it's ease of use?4 | high |
| Boltic 1 | 1 | Boltic | No | How would you rate it's ease of use? | N/a |
this is how I want the data to look like:
| Uniq ID | ResponseID | Program | Ease of Use | Ease of Use Unsure | Easily Acquired | Easily Acquired Unsure |
| Cubic 1 | 1 | Cubic | Low | N/a | med | N/a |
| Boltic 1 | 1 | Bolitc | N/a | N/a | N/a | N/a |
| Cubic 2 | 2 | Cubic | N/a | N/a | N/a | N/a |
| Boltic 2 | 2 | Boltic | High | N/a | high | N/a |
if someone could please help me with the duplicate issue, I would really appreicate it
3 Replies
load your data without headers
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("3Y/NTsNADIRfxco5EqUXjhW0B3qJKm4o5OAmTtbSZh32RyFvjxvoj6rSB+Ay8o4+e3bKMnujMIgLtN1kefbsCSZJ0GLPltHDyNFANATrtOcadl46j/1K0VcZYZRkm3nBYyTg+JEWi+VTAMJAIC2kQIpu25mpjaibXEia0oo/UNMvlcNg5yX6Giyyux1g1FKK7aRZUKODPQHWn4k9Nav7SfN/flh23Y28v7u/iI1afvi35au8zB71wjsFVSujavGAqj01p7mQ83ilhwPL+8jxuuHOnKyLR1V9Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t]), Custom1 = let a={"Cubic","Boltic"},b=Table.ToRows(Source) in #table({"Uniq ID","ResponseID","Program","Ease of Use","Ease of Use Unsure","Easily Acquired","Easily Acquired Unsure"},List.TransformMany(List.Skip(b),each List.Split(List.Skip(List.Zip({b{0},_})),5),(x,y)=>let c=List.Select(a,each Text.Contains(y{0}{0},_)){0}? in {c&" "&Text.From(x{0}),x{0},c}&List.Zip(List.Skip(y)){1})) in Custom1I am not understanding your approach, could you explain the formula below? Appreciate your help!
wdx223_Daniel also, my data source in a excel sheet and I have many other columns in my excel sheet so I am not really understanding your formula above, if you could please help that would be much appreciated
