transform row into column

[PSB]

How to convert column 3 value into row in Power Query?

Before
S_NodeId	S_Cell	Value1234.1	Value1234.2
M1WA6375A	K1WA6375A21	gNBId	1351878
M1WA6375A	K1WA6375A22	nRPCI	215
M1WA6375A	K1WA6375A23	cellLocalId	303

After
S_NodeId	S_Cell	gNBId	nRPCI	cellLocalId
M1WA6375A	K1WA6375A21	1351878	215	303

[PhilipTreacy · Accepted solution]

Hi @PSB 

Download example file with code

This works

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8jUMdzQzNjd1VNJR8oaxjQyBvHQ/J88UIG1obGpoYW6hFKuDS7URkJcXFODsCaSNDE3xqDQG8pJTc3J88pMTc8CmGxsYK8XGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [S_NodeId = _t, S_Cell = _t, Value1234.1 = _t, Value1234.2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"S_NodeId", type text}, {"S_Cell", type text}, {"Value1234.1", type text}, {"Value1234.2", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Value1234.1]), "Value1234.1", "Value1234.2"),
    #"Filled Up" = Table.FillUp(#"Pivoted Column",{"nRPCI", "cellLocalId"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([gNBId] = 1351878))
in
    #"Filtered Rows"

But may need some teaking to work with a larer data set.  Post back here if you are having issues with the rest of your data.

regards

Phil

[PSB]

I followed same steps from your example and still getting error.

[PhilipTreacy]

@PSB 

Your initial data/example is laid out differently to the 2nd lot of data you've shown.

Please supply some representative sample data so we don't have to type everything in by hand.

regards

Phil

[PSB]

Hello Philip, 

Here is the data.

I want to convert "Value1234.1" to row with it's respective values from first three columns and Value1234.2 column. 

Example: 

Data:

S_NodeId	S_Cell	Attribute	Value1234.1	Value1234.2
M1WA6375A	K1WA6375A31	lwNeighborRel	gNBId	1314147
M1WA6375A	K1WA6375A31	lwNeighborRel	gNBId	1344046
M1WA6375A	K1WA6375A31	lwNeighborRel	gNBId	1336352
M1WA6375A	K1WA6375A31	lwNeighborRel	gNBId	1344046
M1WA6375A	K1WA6375A31	lwNeighborRel	gNBId	1336352
M1WA6375A	K1WA6375A31	lwNeighborRel	gNBId	1336352
M1WA6375A	K1WA6375A31	lwNeighborRel	gNBId	1320445
M1WA6375A	K1WA6375A31	lwNeighborRel	gNBId	1337382
M1WA6375A	K1WA6375A31	lwNeighborRel	gNBId	1320445
M1WA6375A	K1WA6375A31	lwNeighborRel	gNBId	1314621
M1WA6375A	K1WA6375A31	lwNeighborRel	gNBId	1351878
M1WA6375A	K1WA6375A31	lwNeighborRel	gNBId	1346518
M1WA6375A	K1WA6375A31	lwNeighborRel	gNBId	1322062
M1WA6375A	K1WA6375A31	lwNeighborRel	gNBId	1341083
M1WA6375A	K1WA6375A31	lwNeighborRel	gNBId	1320445
M1WA6375A	K1WA6375A31	lwNeighborRel	gNBId	1314148
M1WA6375A	K1WA6375A31	lwNeighborRel	gNBId	1333527
M1WA6375A	K1WA6375A31	lwNeighborRel	gNBId	1314256
M1WA6375A	K1WA6375A31	lwNeighborRel	gNBId	1337382
M1WA6375A	K1WA6375A31	lwNeighborRel	cellLocalId	1
M1WA6375A	K1WA6375A31	lwNeighborRel	cellLocalId	2
M1WA6375A	K1WA6375A31	lwNeighborRel	cellLocalId	2
M1WA6375A	K1WA6375A31	lwNeighborRel	cellLocalId	1
M1WA6375A	K1WA6375A31	lwNeighborRel	cellLocalId	1
M1WA6375A	K1WA6375A31	lwNeighborRel	cellLocalId	3
M1WA6375A	K1WA6375A31	lwNeighborRel	cellLocalId	2
M1WA6375A	K1WA6375A31	lwNeighborRel	cellLocalId	302
M1WA6375A	K1WA6375A31	lwNeighborRel	cellLocalId	1
M1WA6375A	K1WA6375A31	lwNeighborRel	cellLocalId	2
M1WA6375A	K1WA6375A31	lwNeighborRel	cellLocalId	3
M1WA6375A	K1WA6375A31	lwNeighborRel	cellLocalId	1
M1WA6375A	K1WA6375A31	lwNeighborRel	cellLocalId	1
M1WA6375A	K1WA6375A31	lwNeighborRel	cellLocalId	303
M1WA6375A	K1WA6375A31	lwNeighborRel	cellLocalId	3
M1WA6375A	K1WA6375A31	lwNeighborRel	cellLocalId	1
M1WA6375A	K1WA6375A31	lwNeighborRel	cellLocalId	303
M1WA6375A	K1WA6375A31	lwNeighborRel	cellLocalId	2
M1WA6375A	K1WA6375A31	lwNeighborRel	cellLocalId	303
M1WA6375A	K1WA6375A31	lwNeighborRel	nRPCI	190
M1WA6375A	K1WA6375A31	lwNeighborRel	nRPCI	29
M1WA6375A	K1WA6375A31	lwNeighborRel	nRPCI	649
M1WA6375A	K1WA6375A31	lwNeighborRel	nRPCI	19
M1WA6375A	K1WA6375A31	lwNeighborRel	nRPCI	702
M1WA6375A	K1WA6375A31	lwNeighborRel	nRPCI	644
M1WA6375A	K1WA6375A31	lwNeighborRel	nRPCI	148
M1WA6375A	K1WA6375A31	lwNeighborRel	nRPCI	853
M1WA6375A	K1WA6375A31	lwNeighborRel	nRPCI	162
M1WA6375A	K1WA6375A31	lwNeighborRel	nRPCI	694
M1WA6375A	K1WA6375A31	lwNeighborRel	nRPCI	656
M1WA6375A	K1WA6375A31	lwNeighborRel	nRPCI	102
M1WA6375A	K1WA6375A31	lwNeighborRel	nRPCI	42
M1WA6375A	K1WA6375A31	lwNeighborRel	nRPCI	860
M1WA6375A	K1WA6375A31	lwNeighborRel	nRPCI	170
M1WA6375A	K1WA6375A31	lwNeighborRel	nRPCI	390
M1WA6375A	K1WA6375A31	lwNeighborRel	nRPCI	44
M1WA6375A	K1WA6375A31	lwNeighborRel	nRPCI	406
M1WA6375A	K1WA6375A31	lwNeighborRel	nRPCI	68

[PSB]

static

[Dinesh_Suranga]

@PSB ,

Hi,

Is Column "Value1234.1" values dynamic or static?

Thank you

[PSB]

static