New column concatenating the previous 3 columns.

[eddd83]

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 

[Anonymous]

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

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.

[smpa01 · Accepted solution]

@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"