M Language Marge Column Data in POWER QUERY TextCombine Function

text.combine m language function in excel and power bi. POwer bi tutorial, power query tutorial

There are two way to combine or marge data or value of two column into single column in POWER QUERY. First You can use ampersand option as like excel and Second process is use TEXT.COMBINE M-LANGUAGE function.

PROCESS 1 - TEXT.COMBINE M LANGUAGE

STEP 1 - Open Power Query window (in Excel or in Power BI)

STEP 2 - Load Data into Power Query

STEP 3 - Go to ADD COLUMN in menu bar and select CUSTOM COLUMN

STEP 4 - Different Syntax and example of TEXT.COMBINE M Language function

Text.Combine({"1st text","2nd text"})

If you want to add space middle of two text

=Text.Combine({"1st text","2nd text"}," ")

If you want to comma middle of two text

=Text.Combine({"1st text","2nd text"},",")

If want to combine data of two or multiple column

= Text.Combine({[1st column],[2nd column]},",")

If want to combine data from column and manual data, syntax will be

= Text.Combine({[1st column],[2nd column],"Manual Text"},",")

Let see one example

STEP 5 - Use TEXT.COMBINE to custom and unique output

PROCESS 2 - USE AMPERSAND OPTION TO MARGE COLUMN DATA

STEP 1 - Click on ADD COLUMN in menu bar and click on CUSTOM COLUMN

STEP 2 - Using Ampersand, same like excel.

Please find the tutorial how to use ampersand in excel

https://www.myaccountingtricks.com/2020/06/concatenate-marge-combine-data-excel-spreadsheet.html

STEP 3 - By using ampersand option, you will get beautiful merged data

Please find the video