Add new row as datetime using Power Query

[Anonymous]

I'm trying to add new rows to a dataset which I get using power query, but I don't know the right way to add datetimes.

This is my query:

#"New Rows" = Table.InsertRows(#"Filtered Rows",Table.RowCount(#"Filtered Rows"),{[codunidade = 1435883] , [codunidade = 918465] , [codunidade = 1435885]})

I'd like to insert a specific datetime for each row. Something like this:

#"New Rows" = Table.InsertRows(#"Filtered Rows",Table.RowCount(#"Filtered Rows"),{[codunidade = 1435883, date = "2021-12-31T23:59:59"] , [codunidade = 918465, date = "2021-12-31T23:59:59"] , [codunidade = 1435885, date = "2021-12-31T23:59:59"]})

How could I do that?

[Anonymous]

Hey guys, thank you for all the answers.

Was just missing the right function: 

DateTime.FromText( )

[Anonymous · Accepted solution]

Hi @Anonymous ，

Not quite sure what you are looking for. Do you want to add a column with a specific date for each row or add multiple rows with a specific date? Could you please provide some raw example data and the final table data you want? I have created a sample file(see attachment) for you, please check if it is the result you want...

Situation 1. Insert rows

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddBLCsAgDATQqxTXgmbyUXsV8f7XaKkRulDIbt5iJr0HkmLCOcRASJCEDLrAt7b3wohLkAs9Criwo2AX5SjERd0KNsXq0bYCrVqZgvNWiJbqa5m2Qg3zH+nL8cvHAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [codunidade = _t, date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"codunidade", Int64.Type}, {"date", type datetime}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [date] = #datetime(2021, 12, 31, 23, 59, 59)),
    #"New Rows" = Table.InsertRows(#"Filtered Rows",Table.RowCount(#"Filtered Rows"),{[codunidade = 1435883, date = DateTime.FromText("2021-12-31T23:59:59")] , [codunidade = 918465, date = DateTime.FromText("2021-12-31T23:59:59")] , [codunidade = 1435885, date = DateTime.FromText("2021-12-31T23:59:59")]})
in
    #"New Rows"

Situation 2. add date column

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc7JDQAhDAPAXvLmkYsctSD6b2PJCqR8R7bltYDUTQVhQBjs8YAOEGITPpLRQA44NdAayQtik2sk5QJnmFfiVXR6YIdp/B+pyv4A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [codunidade = _t, Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"codunidade", Int64.Type}, {"Sales", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Sales] >= 90),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each DateTime.FromText("2021-12-31T23:59:59"))
in
    #"Added Custom"

Best Regards

[LynnW]

Hi I have similar question: 

Every day I will get a total cost summary like this:

1. Day 1 - 09/15/2025

Total cost summary:

Category	Cost
Fruit	120
Vegetable	200
Meat	350

After day 1, I want to have a table:

Date	Category	Cost
09/15/2025	Fruit	120
09/15/2025	Vegetable	200
09/15/2025	Meat	350

2. Day-2 09/16/2025

Total cost summary:

Category	Cost
Fruit	100
Vegetable	250
Meat	200

After day 2, I want to have a table:

Date	Category	Cost
09/15/2025	Fruit	120
09/15/2025	Vegetable	200
09/15/2025	Meat	350
09/16/2025	Fruit	100
09/16/2025	Vegetable	250
09/16/2025	Meat	200

3. Day 3 09/17/2025

Total cost summary:

Category	Cost
Fruit	150
Vegetable	220
Meat	300

After day 1, I want to have a table:

Date	Category	Cost
09/15/2025	Fruit	120
09/15/2025	Vegetable	200
09/15/2025	Meat	350
09/16/2025	Fruit	100
09/16/2025	Vegetable	250
09/16/2025	Meat	200
09/17/2025	Fruit	150
09/17/2025	Vegetable	220
09/17/2025	Meat	300

Question: How can I add new rows to table with date?

[sevenhills]

Try DateTime.FromText ... 

date = DateTime.FromText("2021-12-31T23:59:59")

Sample in M Query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUUpMBJFGBkaGuoZGusaGIUbGVqaWQKQUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type datetime}}),
    #"New Rows" = Table.InsertRows(#"Changed Type", Table.RowCount(#"Changed Type"), {[Column1 = "b", Column2 = "bbb", Column3 = DateTime.FromText("2021-12-31T23:59:59") ], [Column1 = "c", Column2 = "ccc", Column3 = DateTime.FromText("2021-12-31T23:59:59")]})
in
    #"New Rows"

[parry2k]

@Anonymous what is the logic to add a date?