Column to Row

[AllanBerces]

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

[danextian]

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

[ryan_mayu · Accepted solution]

@AllanBerces 

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 this

let
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

[AllanBerces]

Hi @ryan_mayu @danextian @Shai_Karmani thank you very much working as i need

[Shai_Karmani]

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 Karmani

Let's connect in LinkedIn