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

[nichanallseg]

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

[nichanallseg]

It works! Thank you

[HotChilli]

Add a custom column {1..12}

Then Expand it to new rows.

[Vijay_A_Verma · Accepted solution]

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"