cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Mapping companionship between weeds

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Mapping companionship between weeds

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

1 REPLY 1
Highlighted
Super User IV
Super User IV

Re: Mapping companionship between weeds

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Helpful resources

Announcements
Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Top Solution Authors