Issue - Pivot/ Unpivot Scenario

[KR300]

Hi All,

I want to change below data 

FixVersion         Stage       StageStatus

SFP.R.11.8         A              Completed

SFP.R.11.8         B              Completed

SFP.R.11.8         C              In Progress

SFP.R.11.8         D              Not Started

SFP.R.11.9         A              Completed

SFP.R.11.9         B              Completed

SFP.R.11.9         C              In Progress

SFP.R.11.9         D              Not Started

to like below

FixVersion                A                            B                                 C                            D                   

SFP.R.11.8        Completed               Completed                 In Progress              Not Started

SFP.R.11.9        Completed               Completed                 In Progress              Not Started

help me out here.. 

[BeaBF · Accepted solution]

@KR300 Hi! Here the power query code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCnYL0AvSMzTUs1BQ0lFyVAACIO2cn1uQk1qSmqIUq4OuxokINc5QNZ55CgFF+elFqcXFWFS5QFX55ZcoBJckFmGYZUmEmyyJcJMlUW6yxOmmWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"FixVersion " = _t, Stage = _t, StageStatus = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"FixVersion ", type text}, {"Stage", type text}, {"StageStatus", type text}}),
#"Pivoted Column" = Table.Pivot(
Table.TransformColumnTypes(#"Changed Type", {{"Stage", type text}, {"StageStatus", type text}}),
List.Distinct(#"Changed Type"[Stage]),
"Stage",
"StageStatus"
)
in
#"Pivoted Column"

from:

you'll obtain:

if it's ok, please accept my answer as solution!

BBF

[mussaenda · Accepted solution]

Hi @KR300 ,

Have you tried to use the Pivot/Unpivot option in Power Query?

[BeaBF · Accepted solution]

@KR300 Hi! Here the power query code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCnYL0AvSMzTUs1BQ0lFyVAACIO2cn1uQk1qSmqIUq4OuxokINc5QNZ55CgFF+elFqcXFWFS5QFX55ZcoBJckFmGYZUmEmyyJcJMlUW6yxOmmWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"FixVersion " = _t, Stage = _t, StageStatus = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"FixVersion ", type text}, {"Stage", type text}, {"StageStatus", type text}}),
#"Pivoted Column" = Table.Pivot(
Table.TransformColumnTypes(#"Changed Type", {{"Stage", type text}, {"StageStatus", type text}}),
List.Distinct(#"Changed Type"[Stage]),
"Stage",
"StageStatus"
)
in
#"Pivoted Column"

from:

you'll obtain:

if it's ok, please accept my answer as solution!

BBF