Forum Discussion
Post Prodigytransform a year into a date with power query editor
You could try this:
Add a column to generate a list of month numbers:
= Table.AddColumn(#"Changed Type", "Month List", each List.Numbers (1, 12))
Choose "Expand to New Rows" to expand the list.
Add a new column to combine the Year value with the month number value. I used the "Column from Examples" feature with the Year and Month List columns selected. Power Query inserted the following code:
= Table.AddColumn(#"Expanded Month List", "Month", each Text.Combine({Text.From([Month List], "en-US"), "/1/", Text.From([Year], "en-US")}), type text)
Finally, transform the Date column from text to date.
When you use "Column from Examples", you need to start typing in the outcome you expect, so that Power Query knows what you are trying to do. In this case, type 1/1/2019, then in the next row type 2/1/2019. It should pre-fill the rest of the rows of the new column.
Post Prodigyhi!
I did the formula to convert the year into dates, however I just noticed that is duplicating values.
Went from
To
As seen the year 2019 appears 24 times, when it should be only 12 times.
How can I get it right?
Thanks!
Hi - In your screenshot of the Applied Steps, it looks like you are expanding the month list twice. There is also a gear icon next to the first "Expanded Month List" which makes me think something else is going on with that step.
The M code to expand the list should be:
= Table.ExpandListColumn(#"Added Custom1", "Month List")
Can you try deleting the last row of code in your query and check the code for the 5th row?
- o59393
Hi!
Still now working, at the moment I added the column with the formula
Table.AddColumn(#"Changed Type", "Month List", each List.Numbers (1, 12))
I get this (step by step)
Column Month List added:
I go to the column added, unselect all and just click on month list
As seen below 4 rows appear, 2 for 2019 and 2 for 2020. Then I click on Expand New rows:
Once I click on expand rows I get this:
If I remove one of the steps added I get an error.
Can you share your pbix? Still dont know why is wrong.
Thanks!
Hi - check your code in the 4th step. It should be:
=Table.AddColumn(#"Changed Type", "Month List", each List.Numbers (1,12))