Mapping companionship between weeds

[johnf]

Hi.

I have an example dataset as follows (simplified version of a 50K row DB - locations over 1K, Weed types ~500)

| Location     |   Weed   | Risk |
|      A       |   Type 1  | High |
|      B       |   Type 1  | Low  |
|      A       |   Type 2  | High |
|      B       |   Type 2  | Med  |
|      A       |   Type 3  | High |

Basically I'm trying to show if there is any companionship to two weed types growing together on a map.

Just wondering if anyone had any thoguhts about the best way to address this? The correlation should be based on risk where if one weed type is risk High and another weed type in the same location is High or Medium then it should be mapped.

Cheers,

John.

[ImkeF · Accepted solution]

You can check this M-code:

let
    Source = YourTable,
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Location", type text}, {"Weed", type text}, {"Risk", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Risk] <> "Low")),
    #"Grouped Rows1" = Table.Group(#"Filtered Rows", {"Location"}, {{"All", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows1", "Filter", each if Table.RowCount([All])>1 then "in" else "out"),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom", each ([Filter] = "in")),
    #"Added Index" = Table.AddIndexColumn(#"Filtered Rows1", "RiskId", 1, 1),
    WeedyCombinations = Table.ExpandTableColumn(#"Added Index", "All", {"Risk", "Weed"}, {"Risk", "Weed"}),
    #"Merged Queries" = Table.NestedJoin(Source,{"Location", "Risk", "Weed"},WeedyCombinations,{"Location", "Risk", "Weed"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"RiskId"}, {"RiskId"})
in
    #"Expanded NewColumn"

If this doesn't meet your request, please add a table that shows your desired results for your sample data.