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...
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"
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"