Forum Discussion

nichanallseg's avatar
nichanallseg
Frequent Visitor
4 years ago
Solved

Write query to insert copied rows, plus one custom column with running number

Hi all,

 

I need help with Power Query. I need to write a query that will help inserting new rows.

 

So here is the structure of the original data table

It contains employee information with the number of work hours per day

 

 


For each employee, I want to insert another 11 copied rows with a new column called "Month" which will be a running number from 1 to 12 (this will be used later to create a computed column that will calculate working hours per month).

The output table should look something like this

 

 


Please kindly help as real data contains thousands of employees!

 

Thank you so much in advance.

 

Regards,

Nichanal

Need Help
Show and Tell
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    Just inset a custom column named Month and put following there

    ={1..12}

    Then expand this column to new rows.

    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("i45WMjQwMDBU0lHySsxLVfAGMsyVYnUgwkZAnnNGUWYxmrgxkBeSWJmTX6TgDmSawSVMgDzfzOSM1JxUBWeQFj1TuJwpyI7MXAUPiI5YAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Emp ID" = _t, #"Emp Name" = _t, #"Availability per day" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Emp ID", Int64.Type}, {"Emp Name", type text}, {"Availability per day", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Month", each {1..12}),
    #"Expanded Month" = Table.ExpandListColumn(#"Added Custom", "Month")
in
    #"Expanded Month"

3 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    Just inset a custom column named Month and put following there

    ={1..12}

    Then expand this column to new rows.

    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("i45WMjQwMDBU0lHySsxLVfAGMsyVYnUgwkZAnnNGUWYxmrgxkBeSWJmTX6TgDmSawSVMgDzfzOSM1JxUBWeQFj1TuJwpyI7MXAUPiI5YAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Emp ID" = _t, #"Emp Name" = _t, #"Availability per day" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Emp ID", Int64.Type}, {"Emp Name", type text}, {"Availability per day", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Month", each {1..12}),
    #"Expanded Month" = Table.ExpandListColumn(#"Added Custom", "Month")
in
    #"Expanded Month"
  • HotChilli's avatar
    HotChilli
    Community Champion
    4 years ago

    Add a custom column {1..12}

    Then Expand it to new rows.