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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
eddd83
Resolver I
Resolver I

New column concatenating the previous 3 columns.

concat please.PNG

 

How would I concentate these 3 columns, so that nulls would show up as either null or " ". 

 

For example

1) 1366null494 or 1366494

2) nullCommercial994 or Commercial994

3) 1314Retailnull or 1314Retail 

 

 

1 ACCEPTED SOLUTION
smpa01
Super User
Super User

@eddd83please let me know if it works.

let
    Source = Excel.Workbook(File.Contents("C:\Users\smpa01\Desktop\Book1.xlsx"), null, true),
    Sheet2_Sheet = Source{[Item="Sheet2",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet2_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Meter Ticket", Int64.Type}, {"Customer Group", type text}, {"Volumn", Int64.Type}}),
    Custom1 = Table.TransformColumns(#"Changed Type",{},(x) => Replacer.ReplaceValue(x,null," ")),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(Custom1, {{"Meter Ticket", type text}, {"Volumn", type text}}, "en-US"),{"Meter Ticket", "Customer Group", "Volumn"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged")
in
    #"Merged Columns"
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

You can use the feature "Column from the Examples" to achieve.

 

Open Power Query Editor, select these 3 columns you want to combine, then choose Column from the Examples -> from selection

2018-11-07_9-37-55.png

 

 

After that, Power BI would create a column for you. You just need to fill in the combination result of the first row, then Power BI would automatically help you fill in the results from the other rows.

2018-11-07_9-37-10.png

 

 

 

smpa01
Super User
Super User

@eddd83please let me know if it works.

let
    Source = Excel.Workbook(File.Contents("C:\Users\smpa01\Desktop\Book1.xlsx"), null, true),
    Sheet2_Sheet = Source{[Item="Sheet2",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet2_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Meter Ticket", Int64.Type}, {"Customer Group", type text}, {"Volumn", Int64.Type}}),
    Custom1 = Table.TransformColumns(#"Changed Type",{},(x) => Replacer.ReplaceValue(x,null," ")),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(Custom1, {{"Meter Ticket", type text}, {"Volumn", type text}}, "en-US"),{"Meter Ticket", "Customer Group", "Volumn"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged")
in
    #"Merged Columns"
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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

Top Solution Authors