Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |