Forum Discussion
Post Prodigytransform a year into a date with power query editor
Hi all
I have a CSV with targets for different locations per year 2019 and 2020. To make it easier that csv contains only the column for year, since the target wont change per month.
How can I transform that single year column into a date with PBI's power query?
In other words go from
to
thanks!
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.
13 Replies
- o59393
Hi all
I have a CSV with targets for different locations per year 2019 and 2020. To make it easier that csv contains only the column for year, since the target wont change per month.
How can I transform that single year column into a date with PBI's power query?
In other words go from
to
thanks!
- edhans
Community Champion
Duplicate post. Please don't do that. Causes confusion.
- edhans
You use the #date() function, so #date([Year],1,1) will convert the integer in the [Year] field to Jan 1, 2019. But you didn't say how you expected it to do other days. You can put math in the month and day fields. The #date() function is identical to the DATE() function in Excel - #date(year,month,day)
- o59393
Hi edhans
I just need the first day of each month.
E.g.
1/1/2019
2/1/2019
3/1/2019
and so on.
Still not sure how to make that transformation. I attach the pbix
https://www.mediafire.com/file/m63vzw5ms01icy0/QSE_model_test.pbix/file
Is the table called matriz_objetivos_bottler_2019 that contains the 2019 and 2020 value.
Thanks!
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.
