Forum Discussion
Solved
transform row into column
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 ...
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
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
I followed same steps from your example and still getting error.
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
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