Forum Discussion
Add new row as datetime using Power Query
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?
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
5 Replies
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"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
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?
Anonymous what is the logic to add a date?
