Text combine

[hnorgaar]

Hi

Im trying to combine 3 fields like textjoin

Motor is always not empty, both engine type and HK can be empty. So Motor + EngineType if it contains "Turbo" then "Turbo" else empty and if HK is not empty then + HK + "HK" divided by spaces

And this gives me an error and is missing "HK" in the end
Text.Combine({[Motor], Text.Contains([EngineType], "Turbo", "Turbo"), [HK]}, " ")

result like this "320 xDrive Turbo 125HK" if all is not empty

Thx in advance Henrik

 

[smozgur]

I am not entirely sure about the conditions and the desired output, but the following should provide some information:

The source range:

Motor	EngineType	HK
MotorA	EngıneA
MotorB	EngıneB Turbo
MotorC Turbo		HKC
MotorD	EngineD	HKD

The Power Query M Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Custom = Table.AddColumn(Source, "Custom", each Text.Combine({_[Motor], if Text.Contains(_[Motor] & Text.From(_[EngineType]), "Turbo")??false then "Turbo" else "", Text.From(_[HK])}, " "))
in
    Custom

The result:

Motor	EngineType	HK	Custom
MotorA	EngıneA		MotorA
MotorB	EngıneB Turbo		MotorB Turbo
MotorC Turbo		HKC	MotorC Turbo HKC
MotorD	EngineD	HKD	MotorD HKD

If this doesn't generate the output you want, then please provide sample data as my source range above, so I can modify the code as necessary.

 

[hnorgaar]

I am not entirely sure about the conditions and the desired output, but the following should provide some information:

The source range:

Motor	EngineType	HK
MotorA	EngıneA
MotorB	EngıneB Turbo
MotorC Turbo		HKC
MotorD	EngineD	HKD

The Power Query M Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Custom = Table.AddColumn(Source, "Custom", each Text.Combine({_[Motor], if Text.Contains(_[Motor] & Text.From(_[EngineType]), "Turbo")??false then "Turbo" else "", Text.From(_[HK])}, " "))
in
    Custom

The result:

Motor	EngineType	HK	Custom
MotorA	EngıneA		MotorA
MotorB	EngıneB Turbo		MotorB Turbo
MotorC Turbo		HKC	MotorC Turbo HKC
MotorD	EngineD	HKD	MotorD HKD

If this doesn't generate the output you want, then please provide sample data as my source range above, so I can modify the code as necessary.

Thats working fine, just missing 1 step, checking for HK is not empty then add "HK" in the end of the text
Thx
Henrik

 

[smozgur]

Please try the following version.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Custom = Table.AddColumn(Source, "Custom", 
        each Text.Combine(
            {
                _[Motor], 
                if Text.Contains(_[Motor] & Text.From(_[EngineType]), "Turbo")??false then "Turbo" else "", 
                if Text.From(_[HK]) <> "" then Text.From(_[HK]) & " HK" else ""
            }, " "))
in
    Custom

 

[hnorgaar]

Please try the following version.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Custom = Table.AddColumn(Source, "Custom",
        each Text.Combine(
            {
                _[Motor],
                if Text.Contains(_[Motor] & Text.From(_[EngineType]), "Turbo")??false then "Turbo" else "",
                if Text.From(_[HK]) <> "" then Text.From(_[HK]) & " HK" else ""
            }, " "))
in
    Custom

Hi, yes I figured that out myself and all good now, but thanks for your input, appreciate it

 

[smozgur]

Hi, yes I figured that out myself and all good now, but thanks for your input, appreciate it

You're welcome.
Thanks for the feedback.