Forum Discussion
Compare and replace values in a table with the higher value
I have table "Days" like below:
| Day | Severity |
| Monday | Low |
| Friday | High |
| Wednesday | Low |
| Wednesday | High |
| Saturday | Medium |
| Wednesday | Medium |
| Monday | Critical |
How can I compare values in "Severity" based on the value in "Day", and replace with the highest value? Example output below.
| Day | Severity |
| Monday | Critical |
| Friday | High |
| Wednesday | High |
| Wednesday | High |
| Saturday | Medium |
| Wednesday | High |
| Monday | Critical |
I also have a table "Severities" like below, which shows the ranking of severities:
| Index | Severity |
| 1 | Critical |
| 2 | High |
3 | Medium |
| 4 | Low |
Hi Anonymous ,
Try the following code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s3PS0msVNJR8skvV4rViVZyK8qECHhkpmeARcJTU/JSi1FVIYvBFQYnlpQWQcR8U1MyS3MxlCIJwy12LsosyUxOzFGKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Day = _t, Severity = _t]), #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type), #"Changed Type" = Table.TransformColumnTypes(#"Added Index",{{"Day", type text}, {"Severity", type text}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Severity"}, Severities, {"Severity"}, "Severities", JoinKind.LeftOuter), #"Expanded Severities" = Table.ExpandTableColumn(#"Merged Queries", "Severities", {"Index"}, {"Severities.Index"}), #"Grouped Rows" = Table.Group(#"Expanded Severities", {"Day"}, {{"AllRows", each _, Value.Type(#"Expanded Severities")}, {"Minimum Index", each List.Min([Severities.Index]), type number}}), #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Index"}, {"AllRows.Index"}), #"Merged Queries1" = Table.NestedJoin(#"Expanded AllRows", {"Minimum Index"}, Severities, {"Index"}, "Severities", JoinKind.LeftOuter), #"Expanded Severities1" = Table.ExpandTableColumn(#"Merged Queries1", "Severities", {"Severity"}, {"Severities.Severity"}), #"Sorted Rows" = Table.Sort(#"Expanded Severities1",{{"AllRows.Index", Order.Ascending}}), #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"AllRows.Index", "Minimum Index"}) in #"Removed Columns"If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
WinnizIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
- amitchandak
Super User
Anonymous , I am not clear on the logic of how you got table two. Do you have multiple date data in table 1 and you updated that with same status of weekday (highest)
If you filter table 1 for just "Wednesday", you get the table below:
Day Severity Wednesday Low
Wednesday High Wednesday Medium The highest severity in this group is "High", so we replace the other values with this value.
Same applies if you filter for "Monday". There are two severities on that day, "Low" and "Critical". Critical is higher, so we replace the "Low" value with "Critical". I hope this clears it up.
- Jihwan_Kim
Severity Replace CC =
VAR currentday = Days[Day]
VAR _comparisontable =
ADDCOLUMNS (
SUMMARIZE ( FILTER ( Days, Days[Day] = currentday ), Days[Day], Days[Severity] ),
"@severityindex", RELATED ( Severities[Index] )
)
VAR _severityselect =
MINX (
FILTER (
_comparisontable,
[@severityindex] = MINX ( _comparisontable, [@severityindex] )
),
Days[Severity]
)
RETURN
_severityselecthttps://www.dropbox.com/s/d6yt2hod83g2jx5/sabre.pbix?dl=0
v-kkf-msftCommunity Support
Hi Anonymous ,
Try the following code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s3PS0msVNJR8skvV4rViVZyK8qECHhkpmeARcJTU/JSi1FVIYvBFQYnlpQWQcR8U1MyS3MxlCIJwy12LsosyUxOzFGKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Day = _t, Severity = _t]), #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type), #"Changed Type" = Table.TransformColumnTypes(#"Added Index",{{"Day", type text}, {"Severity", type text}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Severity"}, Severities, {"Severity"}, "Severities", JoinKind.LeftOuter), #"Expanded Severities" = Table.ExpandTableColumn(#"Merged Queries", "Severities", {"Index"}, {"Severities.Index"}), #"Grouped Rows" = Table.Group(#"Expanded Severities", {"Day"}, {{"AllRows", each _, Value.Type(#"Expanded Severities")}, {"Minimum Index", each List.Min([Severities.Index]), type number}}), #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Index"}, {"AllRows.Index"}), #"Merged Queries1" = Table.NestedJoin(#"Expanded AllRows", {"Minimum Index"}, Severities, {"Index"}, "Severities", JoinKind.LeftOuter), #"Expanded Severities1" = Table.ExpandTableColumn(#"Merged Queries1", "Severities", {"Severity"}, {"Severities.Severity"}), #"Sorted Rows" = Table.Sort(#"Expanded Severities1",{{"AllRows.Index", Order.Ascending}}), #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"AllRows.Index", "Minimum Index"}) in #"Removed Columns"If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
WinnizIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
