Calculated Column , Concatenate columns headings, if not null

DAX DAX Calculations
1

EDNA - needed guidance with getting my required result . see excel attached.

Require that a calculated column be created to concatenate column headers if that row has data…is
“<0”. desired result is in the right most column.
CFCombinations.xlsx (225.8 KB)

thanks in advance,

Frankee

2

Hi @Frankee,

Your sample doesn’t seem to match with your description, there is no “<0” only “=X”
Copy this script into a new blank query and amend according to your needs.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pdRLCoAwDATQu3RdIbVW3AueQRDvfw0/FW2a2MUECikIj2FI3Ta3Ou/kCZ7oHOQp5pHymO4RiI3r20xu91lTRIDr5iW8pKJiZF+StYqRsSKZipGDJD8VI5NKPipGjr8pcTMxVysVXCVWgZYZdxt5bXCqSxZ7Cz6Ceh9Ebtxt5LXBoozPN5URlcfBb7jbyGuDtTIKGLYH/U9R5MbdRl4b/JSxHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [C0 = _t, CF1 = _t, CF2 = _t, CF3 = _t, CF4 = _t, CF5 = _t, CF6 = _t, C02 = _t, C1 = _t, C2 = _t, C3 = _t, C4 = _t, C5 = _t, C6 = _t, #"Possible combinations" = _t]),
    ChType = Table.TransformColumnTypes(Source,{{"C02", type number}, {"C1", type number}, {"C2", type number}, {"C3", type number}, {"C4", type number}, {"C5", type number}, {"C6", type number}}),
    DelCol = Table.RemoveColumns(ChType,{"Possible combinations"}),
    AddField = Table.AddColumn(DelCol, "Possible combinations", each Text.Combine( Table.SelectRows( Table.FromColumns( { Record.FieldNames(_), Record.FieldValues(_) }), each [Column2] = "X")[Column1], "-" ))
in
    AddField

I hope this is helpful

3

Hello @Frankee, good to see that you are having progress with your inquiry. Did the response from @Melissa help you solve your inquiry?

If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

4

@Melissa/@EnterpriseDNA - I have not responded, because I was testing in my model.
This worked, however, I was hoping that it would be more of a calculated column type of solution. I don’t think I can incorporate this into my current data model which has more to do with my knowledge of this type of code. The help and solution worked.

Thank you for the help @Melissa .

BTW, I found a round about solution using this, zip method…ALSO EDNA!!!

1 Like
5

Hi @Frankee,

Great to learn your query has been resolved! :+1:
If you want to get more familiar with M code, there’s a course in the portal, link below.

1 Like
6

@Melissa , your suggestion is much appreciated !

1 Like