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.
Hi there
I want to create a calculated column which includes text and also a max date. Below is what I want to do, but M Query doesn't like it because I can only see options for a List.Max which isn't what I want.
Can I do this?
= Table.AddColumn(Between, "Column", each if [Duration] >= 60 then "60 to "Max[Duration] else if [Duration] >= 30 then "30 to 59" else if [Duration] >= 11 then "11 to 29" else "0 to 10")
Solved! Go to Solution.
Hi @Anonymous ,
M language is based on the row levels to get the maximum of a column for example as you refer you need to pick up the List.Max.
Assuming that you want to pick up the maximum value of the duration column you need to do your column like:
= Table.AddColumn(Between, "Column", each if [Duration] >= 60 then "60 to " &
Number.ToText (List.Max(#"PreviousStepName"[Duration])) else if [Duration] >= 30 then "30 to 59" else if [Duration] >= 11 then "11 to 29" else "0 to 10")
I'm assuming that your duration column is in number format that is why we need to use the Number.ToText.
Also using the List.Max you need to reference the step you want to calculate the maximum and the column thats the part
List.Max(#"PreviousStepName"[Duration])
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Anonymous ,
M language is based on the row levels to get the maximum of a column for example as you refer you need to pick up the List.Max.
Assuming that you want to pick up the maximum value of the duration column you need to do your column like:
= Table.AddColumn(Between, "Column", each if [Duration] >= 60 then "60 to " &
Number.ToText (List.Max(#"PreviousStepName"[Duration])) else if [Duration] >= 30 then "30 to 59" else if [Duration] >= 11 then "11 to 29" else "0 to 10")
I'm assuming that your duration column is in number format that is why we need to use the Number.ToText.
Also using the List.Max you need to reference the step you want to calculate the maximum and the column thats the part
List.Max(#"PreviousStepName"[Duration])
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@Anonymous
I think it will be better to use DAX to create the column.
Could you please the sample data and expected output?
Proud to be a Super User!
I can't attach anything to here. Happy to do it in DAX, can't figure it out in there either!
The [Duration] column is a numerical column containing any number ranging from 0 to 1000. At the moment, the highest value in there is 477. Tomorrow it will be different.
I want a new column which is of this logic:
If the [Duration] column is between 0 and 10, it says "0 to 10"
If the [Duration] column is between 11 and 29 it says "11 to 29"
If the [Duration] column is between 30 and 59 it days "30 to 59"
If the [Duration] column is over 60 it says "60 to 477"
@Anonymous ,
If you prefer DAX you can use:
Column =
SWITCH (
TRUE (),
'Table'[Duration] >= 60, "60 to "
& FORMAT ( CALCULATE ( MAX ( 'Table'[Duration] ), ALL ( 'Table'[Duration] ) ), "#" ),
'Table'[Duration] >= 30, "30 to 59",
'Table'[Duration] >= 11; "11 to 29",
"0 to 10"
)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@Anonymous yes this can be done in PQ, I guess you want MAX across the whole data table?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@Anonymous here is a sample script that you can apply in your table, start a blank query, click advanced editor and paste the code
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Rcy5DQAwDALAXahT5HEez2J5/zWCQpHuhIAINBS0iiyBTnZxkEO0X5g/XaSJm1ziIY8/OulKudZFXg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Value", Int64.Type}}),
#"Group" = Table.Group(#"Changed Type", {}, {{"MaxValue", each List.Max([Value]), type number}}),
#"Max Value" = #"Group"{0}[MaxValue],
#"Add Column" = Table.AddColumn(#"Changed Type", "Range",each if [Value] >= 60 then "60 - " & Number.ToText(#"Max Value") else "0-60", type text)
in
#"Add Column"
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |