Forum Discussion
Column to Row
Hi all and good day, anyone can assist me on my table, is it ppossible to convert my table column in to row from PQ or calculated column.
from this
to this
| JOB No. | Article No | QTY | SOUTH | EAST | NNORTH |
| 21951135 | 1000255990 | 1 | 21951135 | ||
| 22481705 | 1001098319 | 1 | 22481705 | ||
| 22658569 | 1001142702 | 1 | 22658569 | ||
| 22702391 | 1001142702 | 1 | 22702391 | ||
| 22917777 | 1000052305 | 1 | 22917777 | ||
| 23162967 | 1001906138 | 1 | 23162967 | ||
| 23339018 | 1000954294 | 1 | 23339018 | ||
| 22584472 | 1000052452 | 1 | 22584472 | ||
| 22663497 | 1000062581 | 1 | 22663497 | ||
| 22732744 | 1001272166 | 1 | 22732744 | ||
| 22744357 | 1000103709 | 1 | 22744357 | ||
| 22744357 | 1000116567 | 1 | 22744357 | ||
| 22744357 | 1000148793 | 1 | 22744357 | ||
| 22868820 | 1001265761 | 1 | 22868820 | ||
| 22919967 | 1002590287 | 1 | 22919967 | ||
| 23242916 | 1002745560 | 1 | 23242916 | ||
| 50006071 | 1002278322 | 1 | 50006071 | ||
| 50022498 | 1002745560 | 1 | 50022498 | ||
| 21486352 | 1000898215 | 1 | 21486352 | ||
| 23257201 | 1000064372 | 1 | 23257201 | ||
| 23254473 | 1000056178 | 1 | 23254473 | ||
| 23130140 | 1000056612 | 1 | 23130140 | ||
| 23331150 | 1000056183 | 1 | 23331150 | ||
| 23332783 | 1000056183 | 1 | 23332783 | ||
| 50004633 | 1000056181 | 1 | 50004633 | ||
| 50069394 | 1000056181 | 1 | 50069394 | ||
| 23198390 | 1001074661 | 1 | 23198390 |
if you want to create a calculated column you can try this.
Column = if(not(ISBLANK('Table'[SOUTH])),"South",if(not(ISBLANK('Table'[EAST])),"East","North"))if you want to do this in PQ, you can try thislet
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZRNaxtBDIb/itlzCPoe6ZhAofRgk8Y9FONT6aFQCJT8fzLendHOxm5Tw3iZfd8HSSPNnk7Tl8Pjbv9yP91ND39ef/34/bPu6ubp+L3+Px++HT/X56eH52N97PeHr3V/vjtNhKGIrPUtAgCpRsBlU9eg7eY1AySOBRqAEM4YHVi1ETB1tWgAChWgBuxmKPUOVAMH/h1IvQOBpf5aDaDES34JpN4ARqOwBmCAIXuvYdWGGpg5AL1FCBUKSSC1sWh1kUJrSqLbGlLPUzKWyBqsGjCPNbUxQmEqIq0GKoRmCaS2AURYewQELhCblFK/DaDpcmT/C4iX4H8Dbu4EvQbTYmvRqY01BEY2jjSAfJtS6r1xVFuF1oAiqpbjvWprBL2cPZQ2fDVjZxobd1npaUAd+/BbEQZg8Sw3TtxY+2h4OGGf1kEbh4+0EGCOhnB5n1J6OlCHi3P4DIvfAGZPuw9c2wUrYHgdoXn6fUDUAUDnK6B5OnA5y4+A2dP7IMYbAKfrPsyeBlhwyAfA4mlF129X9OGDImbvgfScz28=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}, {"(blank).5", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"JOB No.", Int64.Type}, {"Article No", Int64.Type}, {"QTY", Int64.Type}, {"SOUTH", Int64.Type}, {"EAST", Int64.Type}, {"NNORTH", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"NNORTH", "NORTH"}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"JOB No.", "Article No", "QTY"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Value"})
in
#"Removed Columns"pls see the attachment below
4 Replies
You can do this in Power Query without a calculated column.
Select the JOB No., Article No., and QTY columns, then right click and choose Unpivot Other Columns. That collapses SOUTH, EAST, and NNORTH into one Attribute and Value pair, where Attribute holds the region name. Filter the Value column to remove the blanks so only the populated region rows remain, rename Attribute to Region, and you can drop the Value column since it just repeats the JOB No.
If your blanks come through as empty strings instead of nulls, add a Replace Values step on those three columns to turn empty into null first, otherwise the filter will not catch them.
If this helped, a thumbs up and accepting the solution would be appreciated.
Best regards,
Shai Karmaniif you want to create a calculated column you can try this.
Column = if(not(ISBLANK('Table'[SOUTH])),"South",if(not(ISBLANK('Table'[EAST])),"East","North"))if you want to do this in PQ, you can try thislet
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZRNaxtBDIb/itlzCPoe6ZhAofRgk8Y9FONT6aFQCJT8fzLendHOxm5Tw3iZfd8HSSPNnk7Tl8Pjbv9yP91ND39ef/34/bPu6ubp+L3+Px++HT/X56eH52N97PeHr3V/vjtNhKGIrPUtAgCpRsBlU9eg7eY1AySOBRqAEM4YHVi1ETB1tWgAChWgBuxmKPUOVAMH/h1IvQOBpf5aDaDES34JpN4ARqOwBmCAIXuvYdWGGpg5AL1FCBUKSSC1sWh1kUJrSqLbGlLPUzKWyBqsGjCPNbUxQmEqIq0GKoRmCaS2AURYewQELhCblFK/DaDpcmT/C4iX4H8Dbu4EvQbTYmvRqY01BEY2jjSAfJtS6r1xVFuF1oAiqpbjvWprBL2cPZQ2fDVjZxobd1npaUAd+/BbEQZg8Sw3TtxY+2h4OGGf1kEbh4+0EGCOhnB5n1J6OlCHi3P4DIvfAGZPuw9c2wUrYHgdoXn6fUDUAUDnK6B5OnA5y4+A2dP7IMYbAKfrPsyeBlhwyAfA4mlF129X9OGDImbvgfScz28=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}, {"(blank).5", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"JOB No.", Int64.Type}, {"Article No", Int64.Type}, {"QTY", Int64.Type}, {"SOUTH", Int64.Type}, {"EAST", Int64.Type}, {"NNORTH", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"NNORTH", "NORTH"}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"JOB No.", "Article No", "QTY"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Value"})
in
#"Removed Columns"pls see the attachment belowHi ryan_mayu danextian Shai_Karmani thank you very much working as i need
Hi AllanBerces
If you don't need the values of each column, you can create a custom column in pq
if [South] <> null then "South" else if [East] <> null then "East" else if [North] <> null then "North" else null
