Forum Discussion
Pivot creates duplicate values
Hi All,
I am trying to create a pivot for different types in a column. However, it creates multiple TIDs based because of the pivot.
I am aware that I can use fill up or fill down so that I can remove duplicate TIDs to get the data right. However, this cannot be done when there is a null value in between and the process fails.
Also tried to create different tables and merge based on TIDs. However, this needs multiple duplicate or referenced tables and this fails when there are so many types in the pivot column.
Is there a way where we can achieve without using the above mentioned steps even by calculated columns or any other method required to work on.
DATA
| TID | FID | Fname | Fdata |
| 443 | 1 | Type-A | 10 |
| 443 | 2 | Type-B | 15 |
| 443 | 3 | Type-C | 25 |
| 443 | 4 | Type-D | 35 |
| 443 | 5 | Type-E | 60 |
| 466 | 1 | Type-A | 100 |
| 466 | 2 | Type-B | 150 |
| 466 | 3 | Type-C | |
| 466 | 4 | Type-D | 350 |
| 466 | 5 | Type-E | 600 |
| 124 | 1 | Type-A | 120 |
| 124 | 2 | Type-B | 140 |
| 124 | 3 | Type-C | |
| 124 | 4 | Type-D | 330 |
| 124 | 5 | Type-E | 660 |
| 651 | 1 | Type-A | 110 |
| 651 | 2 | Type-B | |
| 651 | 3 | Type-C | 220 |
| 651 | 4 | Type-D | 380 |
| 651 | 5 | Type-E | 607 |
| 726 | 1 | Type-A | |
| 726 | 2 | Type-B | 105 |
| 726 | 3 | Type-C | 205 |
| 726 | 4 | Type-D | 30 |
| 726 | 5 | Type-E | 60 |
Expected output:
| TID | Type-A | Type-B | Type-C | Type-D | Type-E |
| 443 | 10 | 15 | 25 | 35 | 60 |
| 466 | 100 | 150 | 350 | 600 | |
| 124 | 120 | 140 | 330 | 660 | |
| 651 | 110 | 220 | 380 | 607 | |
| 726 | 105 | 205 | 30 | 60 |
Hi labuser1235 ,
Remove the FID column and pivot Fname column with Fdata as value column should work.
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 Replies
Try this PQ
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdI7DsMgEEXRrSBqR4JhwGnz20E6y2X6tNl9TOyR3mNSWLJ1QL4aWJaoWuIU8/Y8P+/X6dI/UlwnEzG5dqkgxeTWl6Goyb0vQ6kmj+2lHf9pzRcgDQlI1BAAhgTcMzTslEVdhCBxhCL9idiBIwru4YhjEq1mF5GRKCIA8FkI7uGIM9IwiflHs7jjCAA8h1SBOIKIIxKIuxHrFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TID = _t, FID = _t, Fname = _t, Fdata = _t]), #"Removed Columns" = Table.RemoveColumns(Source,{"FID"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Fname]), "Fname", "Fdata") in #"Pivoted Column"
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂Hi labuser1235 ,
Remove the FID column and pivot Fname column with Fdata as value column should work.
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
labuser1235 , Looking at the data this seem like case of matrix with TID as row Fname as column and Fdata value.
I am missing something?
labuser1235 Looking at the data this seem like case of matrix with TID as row Fname as column and Fdata value.
I am missing something?
Hi Amit ,
I was expecting to have the output saved in another table with the help of transformations if any with power query so that I can club that with another values in the table visual. Hope this is clear.
Hi labuser1235 ,
I don't think without edit query transactions if you can get a new table.
If in case you don't want a new table, you can use MATRIX visual,
On matrix visual, you can have TID on Row section, FName under Column section and your summarised metric under VALUE section.
If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂
Thanks,
Pragati
Pragati11 I donot want to use matrix visual... I would like the output to get achieved in table visual as I am also clubing it with another data from another tables.
I would like to get that achieved as an output with the help of transformaitons using power query or calculated columns,so that I can use in table visual.
Hi labuser1235 ,
In that case try replacing null values in TID column with a value like "Unknown ID" or "ID Missing".
Then try the usual pivot steps in query editor.
If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂
Thanks,
Pragati
