Forum Discussion

GTL's avatar
GTL
Frequent Visitor
3 years ago
Solved

Group

Hi everyone! I am trying to group rows with similar equipment. For example: group all of my escavators together, all dirt, all dump truck, etc. We are syncing this data directly via an API daily so editing in Power Query would not work. 

 

I used the group feature in the data field and this works to create my groups, however is making no change in my table. It only adds a column instead of combines the row. Please help.

 

Thank you,

Georgia 

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi GTL ,

     

    According to your statement, I think you want to combine the data by group like escavators/dirt/dump truck and so on. 

     

    If the group in screenshot above is correct, I think you can remove [Equipment] in your table visual and then Power BI will summarize your data by group in [Dirt] column. Power BI will summarize data if all data are the same, so if you add [Equipment] column into your visual, it will be expanded.

     

    Here I am confused about whether "Escavtor" in "Escavator" group. If yes, it is hard for us to do in Power BI. I think you need to make sure each character contains the complete category word. Then we can use contain dax to create group column.

     

    Group =
SWITCH (
    TRUE (),
    CONTAINSSTRING ( 'Table'[Equipment], "Escavator" ), "Escavator",
    CONTAINSSTRING ( 'Table'[Equipment], "Dump trucks" ), "Dump trucks",
    CONTAINSSTRING ( 'Table'[Equipment], "dirt" ), "Dirt",
    "Others"
)

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi GTL ,

     

    According to your statement, I think you want to combine the data by group like escavators/dirt/dump truck and so on. 

     

    If the group in screenshot above is correct, I think you can remove [Equipment] in your table visual and then Power BI will summarize your data by group in [Dirt] column. Power BI will summarize data if all data are the same, so if you add [Equipment] column into your visual, it will be expanded.

     

    Here I am confused about whether "Escavtor" in "Escavator" group. If yes, it is hard for us to do in Power BI. I think you need to make sure each character contains the complete category word. Then we can use contain dax to create group column.

     

    Group =
SWITCH (
    TRUE (),
    CONTAINSSTRING ( 'Table'[Equipment], "Escavator" ), "Escavator",
    CONTAINSSTRING ( 'Table'[Equipment], "Dump trucks" ), "Dump trucks",
    CONTAINSSTRING ( 'Table'[Equipment], "dirt" ), "Dirt",
    "Others"
)

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.