How to return a columm name using DAX or M formula?

[Nese_FZ]

Good afternoon

I need help so solve a simple query: 

Consider the following scenario:

I have a table with several projects where each customer is in a row and the date of the last status according to the defined columns. I only have one date on the line, which is the client's last status. The others status are null.

How to create a column that returns the last customer status (column name)?

follow the example table ( in red the expect solution). Thanks!

[Nese_FZ · Accepted solution]

Hi, 

Thanks for your help

I tried but my source is not an excel table (I just used it as an example). It is already a table in Power Query. I was unable to adjust the first step (source)

Could you help me?

thanks

[Anonymous · Accepted solution]

Hi @Nese_FZ ，

Based on your description, you can do some steps as follows.

• Suggestion 1:

You can just unpivot the columns in Power Query.

Result:

• Suggestion 2:

You can create a calculated column as follows.

actual status =

IF(

   ISBLANK('CASE1'[finish]),

   IF(

       ISBLANK('CASE1'[in treatment]),

       "return",

       "in treatment"

    ),

    "finish"

)

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Nese_FZ]

Hi, 

Thanks for your suggestion 2.  I tested and worked also. 

[Fowmy]

@Nese_FZ 

In Power Query, Paste the below on a Blank Query in the Advanced Editor and check the steps.

Only three steps, Select Project and Client > UnPivot Other Columns, Filter Value Column <> Null and remove Value column.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc9LDoAgDATQu7Am6bTy8yzEGxB33l+QoIW4aqCvTMnZxGSsYYBRK5gQSfrhvEoZ5bBK8tdkrAOTlOWZmuCJpQ80uft6J4CH7vFP+pBh7PnINUDLqD4hnrBNA1omLQPBNeleedw=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [project = _t, #"Client ID" = _t, #"in treatment" = _t, finish = _t, return = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"project", "Client ID"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "null")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Value"})
in
    #"Removed Columns"

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Nese_FZ · Accepted solution]

Hi, 

Thanks for your help

I tried but my source is not an excel table (I just used it as an example). It is already a table in Power Query. I was unable to adjust the first step (source)

Could you help me?

thanks

[Nese_FZ]

Hi,

I understood the steps and it was worked.

Thanks for your help