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 | 215 |
| M1WA6375A | K1WA6375A23 | cellLocalId | 303 |
| After | ||||
| S_NodeId | S_Cell | gNBId | nRPCI | cellLocalId |
| M1WA6375A | K1WA6375A21 | 1351878 | 215 | 303 |
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
7 Replies
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
static
static
