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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
claper57
Frequent Visitor

Create custom column with cocatenated value

Hello

My table has 5 columns with column header Col1, Col2, Col3, Col4 and Col5
All of them contains values True or False
In Power Query I will create new custom column with name Col6
This Column will contain concatenated value of Col1-5 headers.
Lets say row 2 has values True,True,False,False,True
Then I want Col6 value to be a concatenation as follows "Col1|Col2|Col5"
Can anybody help me with such a M-code statement?

1 ACCEPTED SOLUTION
AlB
Super User
Super User

Hi @claper57 

Try this as code fro your custome column:

= Text.Combine({if [Col1] then "Col1" else null,if [Col2] then "Col2" else null, if [Col3] then "Col3" else null,if [Col4] then "Col4" else null,if [Col5] then "Col5" else null}, "|")

 


SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

try also this

 

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCikqTVXSgVFuiTnFyDRYOFYnGl0emy6QMjR5bMpjYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Colonna1 = _t, Colonna2 = _t, Colonna3 = _t, Colonna4 = _t, Colonna5 = _t]),
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"Colonna1", type logical}, {"Colonna2", type logical}, {"Colonna3", type logical}, {"Colonna4", type logical}, {"Colonna5", type logical}}),
    cols=Table.ColumnNames(#"Modificato tipo"),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Modificato tipo", "WhereIsTrue", each Text.Combine(List.Transform(List.PositionOf(Record.FieldValues(_),true, Occurrence.All),each cols{_}),"|"))
in
    #"Aggiunta colonna personalizzata"

Thanks - I will try this also - to learn 🙂

AlB
Super User
Super User

Hi @claper57 

Try this as code fro your custome column:

= Text.Combine({if [Col1] then "Col1" else null,if [Col2] then "Col2" else null, if [Col3] then "Col3" else null,if [Col4] then "Col4" else null,if [Col5] then "Col5" else null}, "|")

 


SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

claper57
Frequent Visitor

Thanks - worked 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors