Forum Discussion

frankegg's avatar
frankegg
New Member
3 years ago
Solved

Creating a new table with rows containing substring in a column

Hi   I am very new to PowerBI queries, and hope someone can push me in the right direction 🙂   I have a table as shown below, named RoomTable. I will use this to create a similar table co...
  • FreemanZ's avatar
    FreemanZ
    3 years ago
    Try to create a measure with the code below, then you shall be able to create your expected table visual with this measure:
    Meetingrooms :=
    CALCULATE(
         SUM('RoomTable'[Capacity]),
         FILTER (
             'RoomTable'
             CONTAINS (
                'RoomTable', 
                'RoomTable'[Room], 
                "Meeting"
              )
          )
    )
daXtreme's avatar
daXtreme
Icon for Solution Sage rankSolution Sage
3 years ago

In Power Query denormalize the table first. Each piece of info should be stored in a separate column. So, your room number in one column and the type of room in another. Then you'll be able to filter seamlessly. Filtering by a substring is not the best idea and not Best Practice. Not to mention it slows down DAX.