Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Good evening eveyone!
I have a challenge where i need to count the amount of months each row
For each row i have a start date and end date.
Start Date: 04/01/2016
End Date: 12/01/2018
Answer:
Add a Column "Year 2016" with 9 months
Add a Column "Year 2017" with 12 months
Add a Column "Year 2018" with 12 months
Please note Start Date and End Date may vary to any year (before 2016 or after 2018).
Could you please help me?
Thank you!
JMelo
Solved! Go to Solution.
Not a complete solution!
I am assuming you want a generic way to add columns given any two dates. You can use List.Generate to run a loop for all the years between start and end dates and then within the loop add the column. A reference for the concept is below:
https://potyarkin.ml/posts/2017/loops-in-power-query-m-language/
As for syntax of adding the column you require, you can play with the Add Column from the menu options. Here is an example (after manually entering data):
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkktLlEwNDJW0lEyMNE3MNQ3MjA0A3IMjaAcC6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Contract Name" = _t, #"Start Date" = _t, #"End Date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Contract Name", type text}, {"Start Date", type date}, {"End Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Months in 2016", each 12 - Date.Month([Start Date]))
in
#"Added Custom"
Hi @JMelo ,
What do you mean about below information?
Add a Column "Year 2016" with 9 months
Add a Column "Year 2017" with 12 months
Add a Column "Year 2018" with 12 months
Could you please post your desired result if possible?
Regards,
Daniel He
The desired solution would to add one column for each year within the date interval.
Power Query Table as is in the Database:
Contract Name | Start Date | End Date |
Test 123 | 04/01/2016 | 12/01/2018 |
Power Query Table after data modeling:
Contract Name | Start Date | End Date | Months in 2016 | Months in 2017 | Months in 2018 |
Test 123 | 04/01/2016 | 12/01/2018 | 9 | 12 | 12 |
Any help please?
Not a complete solution!
I am assuming you want a generic way to add columns given any two dates. You can use List.Generate to run a loop for all the years between start and end dates and then within the loop add the column. A reference for the concept is below:
https://potyarkin.ml/posts/2017/loops-in-power-query-m-language/
As for syntax of adding the column you require, you can play with the Add Column from the menu options. Here is an example (after manually entering data):
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkktLlEwNDJW0lEyMNE3MNQ3MjA0A3IMjaAcC6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Contract Name" = _t, #"Start Date" = _t, #"End Date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Contract Name", type text}, {"Start Date", type date}, {"End Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Months in 2016", each 12 - Date.Month([Start Date]))
in
#"Added Custom"