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
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"
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 ryan_mayu danextian Shai_Karmani thank you very much working as i need