Creating a table that is a pivot over another table

[lottieritchie]

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!

Property	Room	Scope
123 Test Street	Bedroom 1	Express Turn Around
123 Test Street	Bedroom 2	Standard Turn Around
345 Test Street	Bedroom 1	Express Turn Around

[v-yalanwu-msft]

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.

[negi007]

@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]

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 

[lottieritchie]

@negi007 Sorry just trying to work out how to upload a file on here, I can't see that I can?