Forum Discussion
To convert data from column into row (based on column value)
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcq1ITS4tySxLVdJRMgJiE6VYnWgl38S8xPTUIiDfEIjBQsGlBalFZZnF+SBRsOLYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Job level" = _t, #"No of position required in 2022" = _t, #"No of position required in 2023" = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Job level"}, "Year positions required", "Value"), #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","",null,Replacer.ReplaceValue,{"Value"}), #"Filtered Rows" = Table.SelectRows(#"Replaced Value", each ([Value] <> null)), #"Extracted Last Characters" = Table.TransformColumns(#"Filtered Rows", {{"Year positions required", each Text.End(_, 4), type text}}), #"Added Prefix" = Table.TransformColumns(#"Extracted Last Characters", {{"Year positions required", each "Year " & _, type text}}), #"Changed Type" = Table.TransformColumnTypes(#"Added Prefix",{{"Job level", type text}, {"Year positions required", type text}, {"Value", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "List", each {1..[Value]}), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value"}), #"Expanded List" = Table.ExpandListColumn(#"Removed Columns", "List"), #"Removed Columns1" = Table.RemoveColumns(#"Expanded List",{"List"}), #"Added Custom1" = Table.AddColumn(#"Removed Columns1", "No of position required", each 1), #"Added Index" = Table.AddIndexColumn(#"Added Custom1", "Position ID", 1, 1, Int64.Type), #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Position ID", "Job level", "Year positions required", "No of position required"}) in #"Reordered Columns"
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcq1ITS4tySxLVdJRMgJiE6VYnWgl38S8xPTUIiDfEIjBQsGlBalFZZnF+SBRsOLYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Job level" = _t, #"No of position required in 2022" = _t, #"No of position required in 2023" = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Job level"}, "Year positions required", "Value"),
#"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","",null,Replacer.ReplaceValue,{"Value"}),
#"Filtered Rows" = Table.SelectRows(#"Replaced Value", each ([Value] <> null)),
#"Extracted Last Characters" = Table.TransformColumns(#"Filtered Rows", {{"Year positions required", each Text.End(_, 4), type text}}),
#"Added Prefix" = Table.TransformColumns(#"Extracted Last Characters", {{"Year positions required", each "Year " & _, type text}}),
#"Changed Type" = Table.TransformColumnTypes(#"Added Prefix",{{"Job level", type text}, {"Year positions required", type text}, {"Value", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "List", each {1..[Value]}),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value"}),
#"Expanded List" = Table.ExpandListColumn(#"Removed Columns", "List"),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded List",{"List"}),
#"Added Custom1" = Table.AddColumn(#"Removed Columns1", "No of position required", each 1),
#"Added Index" = Table.AddIndexColumn(#"Added Custom1", "Position ID", 1, 1, Int64.Type),
#"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Position ID", "Job level", "Year positions required", "No of position required"})
in
#"Reordered Columns"
Hi,
let
Source = Excel.Workbook(File.Contents("C:\Users\xxx.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
_t = ((type text) meta [Serialized.Text = true]),
#"Job level" = _t,
#"No of position required in 2022" = _t,
#"No of position required in 2023" = _t,
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Job level"}, "Year positions required", "Value"),
#"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","",null,Replacer.ReplaceValue,{"Value"}),
#"Filtered Rows" = Table.SelectRows(#"Replaced Value", each ([Value] <> null)),
#"Extracted Last Characters" = Table.TransformColumns(#"Filtered Rows", {{"Year positions required", each Text.End(_, 4), type text}}),
#"Added Prefix" = Table.TransformColumns(#"Extracted Last Characters", {{"Year positions required", each "Year " & _, type text}}),
#"Changed Type" = Table.TransformColumnTypes(#"Added Prefix",{{"Job level", type text}, {"Year positions required", type text}, {"Value", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "List", each {1..[Value]}),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value"}),
#"Expanded List" = Table.ExpandListColumn(#"Removed Columns", "List"),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded List",{"List"}),
#"Added Custom1" = Table.AddColumn(#"Removed Columns1", "No of position required", each 1),
#"Added Index" = Table.AddIndexColumn(#"Added Custom1", "Position ID", 1, 1, Int64.Type),
#"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Position ID", "Job level", "Year positions required", "No of position required"})
in
#"Reordered Columns"
This code resulted to Job level is not found.. anything I can do to fix it?
Please check in your table whether Job level column appears or not or does it have some space/line break in that column name.