Conditional merge columns in Query Editor

[amien]

I have this table:

Field1	Field2	Field3
1
	5
		2

I want to column merge these 3 into 1 field and put a prefix in the new column:

Field1	Field2	Field3	Field4
1			Field1_1
	5		Field2_5
		2	Field3_2

I was trying something like this : If(Field1 > 0,"Field1_" & [Field1], If(Field2 > 0,"Field2_"  & [Field2]

Thanks in advanced

[LarsSchreiber · Accepted solution]

Hi @amien,

premising that your data come from Excel, from a table that is called "table1" use the following M code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"Field1", Int64.Type}, {"Field2", Int64.Type}, {"Field3", Int64.Type}}),
    Replace = Table.ReplaceValue(ChangedType,null,0,Replacer.ReplaceValue,{"Field1", "Field2", "Field3"}),
    AddColumn = Table.AddColumn(Replace, "Field4", each if [Field1]>0 then 
		                                                  "Field1_" & Number.ToText([Field1]) 
		                 		                        else if [Field2]>0 then 
						                                   "Field2_" & Number.ToText([Field2]) 
						                                else 
						                                   "Field3_" & Number.ToText([Field3]) 
	           )								   
in
    AddColumn

Hope that helps,

Regards,

Lars

[amien]

Thanks Lars