Pivot creates duplicate values

[labuser1235]

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

[v-jayw-msft · Accepted solution]

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.

[nandukrishnavs]

@labuser1235 

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 🙂

[Pragati11]

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

[labuser1235]

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

[Pragati11]

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

[amitchandak]

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

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.

[amitchandak]

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