transform a year into a date with power query editor

[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!

[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]

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!

[Anonymous · Accepted solution]

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.  

[o59393]

Hi @Anonymous 

Appreciate the help! almost have it.

This is what I get when I use column from examples

What should i do?

Thanks!

[Anonymous]

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.  

[o59393]

hi!

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!

[Anonymous]

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!

[Anonymous]

Hi - check your code in the 4th step.  It should be:

=Table.AddColumn(#"Changed Type", "Month List", each List.Numbers (1,12))

[o59393]

It worked, thanks a mill 🙂

[edhans]

Don't use the column by example.

Use #date([Year],[Month.date.list.date.or.whatever.that.column.is.called],1)