Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kfschaefer
Helper IV
Helper IV

Create new column concatenation of Month and Year

what is the correct syntax for creating a new column in existing query that has various fields.  I have two fields Month_Name and Year.  I want to create a new column that concetnates both columns togeth.  How do I modify the add column dax statement to include the concatenation?

 

Thanks,

Karen

 

= Table.AddColumn(#"Added Custom", "MMM_YYYY", each Text.From([Month_name]) & " " & Text.From([Year]))

1 ACCEPTED SOLUTION
edhans
Super User
Super User


@kfschaefer wrote:

what is the correct syntax for creating a new column in existing query that has various fields.  I have two fields Month_Name and Year.  I want to create a new column that concetnates both columns togeth.  How do I modify the add column dax statement to include the concatenation?

 

Thanks,

Karen

 

= Table.AddColumn(#"Added Custom", "MMM_YYYY", each Text.From([Month_name]) & " " & Text.From([Year]))


The above isn't DAX, that is M in Power query. You don't need Text.From() around Month_name as I assume that is text already. You do around [Year] assuming that is a 4 digit year. Your statement should be in your Dates table (or whatever table you have dates you want to convert, but you should do it in your Dates table anyway then join to the other table, but that is another post):

 

=Date.ToText([Date],"MMM" ) & "_" & Text.From(Date.Year([Date]))

That will use the date field to get the month and year, and put it in MMM_YYYY format as text.

 

If you want to do it in DAX by creating a custom column (which I do not recommend) you'd use this in your Dates table:

 

MMM_YYYY = FORMAT(Dates[Date],"MMM") & "_" & FORMAT(Dates[Date],"yyyy")


Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

 

LOL this what you guys have to do to get a Date/Month?!?! WOW i'm a Tableau Cert Professional. Started doing a part-time job using PowerBI. Yikes. I thought this was 'competition' to Tableau and holy hell it's not. DAX is downright pathetic. I am better off literally writing code in SQL. You can't even click and drag fields into the formula? The formula isn't seperate from visual? Can't interact with visual while formula still open in different window.

 

Seriously. Microsoft ALWAYS drops the ball, but this one is REALLY bad. If I was a developer I would be recommending any client away from this product. I mean imagine having to use 5 MS appilications/products to build a dashboard. Whew. Yeah I'll be writing all my code elsewhere. This is absolute garbage.

Hi @Anonymous - glad to have you here. 

This isn't DAX. It is the M code for the ETL component of Power BI. You know, the thing Tableau clients have to pay Alteryx licenses for.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
edhans
Super User
Super User


@kfschaefer wrote:

what is the correct syntax for creating a new column in existing query that has various fields.  I have two fields Month_Name and Year.  I want to create a new column that concetnates both columns togeth.  How do I modify the add column dax statement to include the concatenation?

 

Thanks,

Karen

 

= Table.AddColumn(#"Added Custom", "MMM_YYYY", each Text.From([Month_name]) & " " & Text.From([Year]))


The above isn't DAX, that is M in Power query. You don't need Text.From() around Month_name as I assume that is text already. You do around [Year] assuming that is a 4 digit year. Your statement should be in your Dates table (or whatever table you have dates you want to convert, but you should do it in your Dates table anyway then join to the other table, but that is another post):

 

=Date.ToText([Date],"MMM" ) & "_" & Text.From(Date.Year([Date]))

That will use the date field to get the month and year, and put it in MMM_YYYY format as text.

 

If you want to do it in DAX by creating a custom column (which I do not recommend) you'd use this in your Dates table:

 

MMM_YYYY = FORMAT(Dates[Date],"MMM") & "_" & FORMAT(Dates[Date],"yyyy")


Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.