To convert data from column into row (based on column value)

[shea]

Hello,

I'm in the midst of creating talent supply and demand dashboard using powerBI and having challenges as my data is as per input below. What I need to do is to :

1. To index the each job row (as it is referring to different position id, since position id is not yet created)

2. To convert the column value into row (based on column value) i.e 2 positions executive required to be captured in different row so that I can index it later (to identify it as different position)

Input:

Job level   |  No of position required in 2022 | No of position required in 2023

---------------------------------------------------------------------------------------

Executive  |      2                                              |            4

Output:

Position ID |    Job level   |  Year positions required  | No of position required 

---------------------------------------------------------------------------------------

1                 |Executive  |      Year 2022                   |            1

2                 |Executive  |       Year 2022                  |            1

3                 |Executive  |      Year 2023                   |            1

4                 |Executive  |      Year 2023                   |            1

5                 |Executive  |      Year 2023                   |            1

6                 |Executive  |      Year 2023                   |            1

Thank you in advanced for your help. 

[Vijay_A_Verma · Accepted solution]

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"

[shea]

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?

[Vijay_A_Verma]

Please check in your table whether Job level column appears or not or does it have some space/line break in that column name.

[shea]

Dear Vijay,

Thanks a bunch! you have made my day 🙂