Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
Hi @Nese_FZ ,
Based on your description, you can do some steps as follows.
You can just unpivot the columns in Power Query.
Result:
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.
Hi @Nese_FZ ,
Based on your description, you can do some steps as follows.
You can just unpivot the columns in Power Query.
Result:
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.
Hi,
Thanks for your suggestion 2. I tested and worked also.
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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
Hi,
I understood the steps and it was worked.
Thanks for your help
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |