How to combine 3 field into 1 ?

[Anonymous]

Hi All

I have below target Table , may i know how to combine below field into 1 :-

SOURCE , 

date_t

GROUP_

Expected result :-

Link field consist of "SINGAPORE 1 January 2010 GROUP_"

Below link is my PBI file :-

https://www.dropbox.com/s/hpoyek0iy2a91ne/TARGET_V001.pbix?dl=0

My raw data :-

SOURCE	date_t	GROUP_	ODSE_target_sales
SINGAPORE	1 January 2010	G1	100,000.00
SINGAPORE	1 February 2010	G1	100,000.00
SINGAPORE	1 March 2010	G1	100,000.00
SINGAPORE	1 April 2010	G1	100,000.00
SINGAPORE	1 May 2010	G1	100,000.00
SINGAPORE	6 June 2010	G1	100,000.00
SINGAPORE	1 July 2010	G1	100,000.00
SINGAPORE	1 August 2010	G1	100,000.00
SINGAPORE	1 September 2010	G1	100,000.00
SINGAPORE	1 October 2010	G1	100,000.00
SINGAPORE	1 November 2010	G1	100,000.00
SINGAPORE	1 December 2010	G1	100,000.00
SINGAPORE	1 January 2010	G2	175,000.00
SINGAPORE	1 February 2010	G2	175,000.00
SINGAPORE	1 March 2010	G2	175,000.00
SINGAPORE	1 April 2010	G2	175,000.00
SINGAPORE	1 May 2010	G2	175,000.00
SINGAPORE	6 June 2010	G2	175,000.00
SINGAPORE	1 July 2010	G2	175,000.00
SINGAPORE	1 August 2010	G2	175,000.00
SINGAPORE	1 September 2010	G2	175,000.00
SINGAPORE	1 October 2010	G2	175,000.00
SINGAPORE	1 November 2010	G2	175,000.00
SINGAPORE	1 December 2010	G2	175,000.00

[themistoklis · Accepted solution]

@Anonymous 

Change your initial DAX formula (not Power Query) to the following one:

Column = 'TARGET'[SOURCE] & FORMAT('TARGET'[date_t], "DD MMMM YYYY") & 'TARGET'[GROUP_]

[PhilipTreacy]

Hi @Anonymous 

Sorry, can't open your PBIX file because the data source is on your computer.

To do this in Power Query

= Table.AddColumn(#"Changed Type", "Custom", each [SOURCE] & " " & Date.ToText([date_t], "dd MMMM yyyy") & " GROUP_" & [GROUP_])

This will give you a string like SINGAPORE 1 January 2010 GROUP_G1

If you don't actually want the text GROUP_ in there, delete it from the line of code above so you just have

= Table.AddColumn(#"Changed Type", "Custom", each [SOURCE] & " " & Date.ToText([date_t], "dd MMMM yyyy") & " " & [GROUP_])

Phil 

[Anonymous]

Hi Philip

Can you share how to do it at PQ ?

Paul

[PhilipTreacy]

Hi @Anonymous 

Here's an Excel file that cotains the data and query.  I haven't done a PBIX file because I'd need to share my source data file too.  You can just copy/paste the code into PBI.

Phil

[Anonymous]

Hi Philip

Mu PBI file 

https://www.dropbox.com/s/708rtbxrordiaiu/TARGET_V002.pbix?dl=0

I still get error :-

[PhilipTreacy]

Hi @Anonymous 

Actually, here's your PBIX file.  I added the line of code to it but can't test because it's data source is on your computer.

Phil

[Anonymous]

Hi Philip

kinldy use ver 3 below file and add your script in :-

https://www.dropbox.com/s/phht2l4h8z5qbkk/TARGET_V003.pbix?dl=0

very sorry As the ver 1 i cannot reload 

[PhilipTreacy]

Hi @Anonymous 

You had the last line of the query mixed up, it should just be

#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [SOURCE] & " " & Date.ToText([date_t], "dd MMMM yyyy") & " " & [GROUP_])

Here's the PBIX file

Phil 

[Anonymous]

Hi Philip

Thank you very much for your help  , it work fine now. May i know how you generate the Script ? 

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

I used the GUI -> Add Column -> Custom Column

Phil

[Anonymous]

Hi Philip

Thank you 

Paul

[amitchandak]

@Anonymous , Try a new column like

new column = [SOURCE] & format(date_t,"DD MMMM YYYY") & [GROUP_]

[Anonymous]

Hi Amit

I get error msg :-

[themistoklis · Accepted solution]

@Anonymous 

Change your initial DAX formula (not Power Query) to the following one:

Column = 'TARGET'[SOURCE] & FORMAT('TARGET'[date_t], "DD MMMM YYYY") & 'TARGET'[GROUP_]