Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
Proud to be a Super User!
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
Proud to be a Super User!
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
Proud to be a Super User!
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 |
static
static
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |