Forum Discussion

amien's avatar
amien
Icon for Helper V rankHelper V
10 years ago
Solved

Conditional merge columns in Query Editor

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

 

Need Help
Tutorial Requests
  • LarsSchreiber's avatar
    LarsSchreiber
    10 years ago

    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

2 Replies

  • LarsSchreiber's avatar
    LarsSchreiber
    Icon for Responsive Resident rankResponsive Resident
    10 years ago

    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