Forum Discussion
Solved
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...
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
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