Forum Discussion

lottieritchie's avatar
lottieritchie
Helper I
5 years ago

Creating a table that is a pivot over another table

Hi, 

 

I have a table that has a record of rooms within houses (see example below). 

What I am trying to achieve is a table, with the list of properties (appearing only once), and if there is any room within that house that requires an Express Turn Around. 

 

What I would have previously done in excel is create a pivot table over my table, and  filtered on where the Scope = Express Turn Around, and then from my list of properties, lookup to see if that property exists in the pivot table. 

 

How do I achieve the same thing (or get the same result) in power BI? 

 

Thank you!

 

PropertyRoomScope 
123 Test StreetBedroom 1Express Turn Around
123 Test Street Bedroom 2Standard Turn Around
345 Test Street Bedroom 1Express Turn Around 
Need Help

4 Replies

  • negi007's avatar
    negi007
    Community Champion
    5 years ago

    lottieritchie Hi will it be possible for you to share your powerbi sample file or more detailed information about your base data and output data. this will help us to provide you a solution. Thanks

    • lottieritchie's avatar
      lottieritchie
      Helper I
      5 years ago

      Hi negi007  thanks for your reply. 

       

      I've got some personal data in my full data set so I've pulled out just the relevant table as a hopefully clearer explanation of what I am trying to achieve. 

       

      You'll see there is a table called 'Scope'. At the moment I have, for example, if you look at the column 'Property', and for example look at 15 The Park, you will see that 3 of the 5 rooms have the status 'Express Turn Around' in the column 'Turn Around'. 

       

      What I would like to get to is a table with each Property only listed once, and if any of the rooms for that property say 'Express Turn Around' then the Turn Around status for the whole property line item would be Turn Around.

       

      I've created this in Excel to show my desired outcome / how I would have done this in excel! 

       

      Thanks for your help 

       

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    5 years ago

    Hi lottieritchie ,


    You could create a new table by the following formula:

    newtable =
var _table=SELECTCOLUMNS('Table2',"1",[Property])
return FILTER('Table1',[Property] in _table&&[Scope]="Express Turn Around")

    The final output is shown below:

    If it's not right, please share with me your expected result.
    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.