Extract the values from a table based on a rule and create a new table

[Satya123]

Hi I am quite new to power bi and trying to learn how to use DAX and other power bi queries. I have been trying but not able to get what I want from my below query.
I hope someone can help.

I have same data in below 2 formats and I want the output table as below. Any data table can be used to get the output

Data table 1 with format 1

Rolename status hostname
Role-A HELD Server-A
Role-A NOT HELD Server-B
Role-A NOT HELD Server-C
Role-B HELD Server-A
Role-B HELD Server-B
Role-B NOT HELD Server-C
Role-C NOT HELD Server-A
Role-C NOT HELD Server-B
Role-C NOT HELD Server-C


Data table 2 format 2

Rolename status hostname status hostname status hostname
Role-A HELD Server-A NOT HELD Server-B NOT HELD Server-C
Role-B HELD Server-A HELD Server-B NOT HELD Server-C
Role-C NOT HELD Server-A HELD Server-B NOT HELD Server-C



I want the output in a table like this as below or How can I display the above table data in Visualization using any graph/filter which can show below output ???


Output Table Format (should contain all unique role names even if no role is HELD against it)

Rolename Server-A Server-B Server-C
Role-A HELD
Role-B HELD HELD
Role-C

 

[peter789]

Power Query is the way to go.
In first case Pivot the data on host name with status as the value column with no aggregation. Then replace "NOT HELD" with blanks.
In the second case you need to transform the data into the same format as the first table. One way is to merge the pairs of columns with a distinctive delimiter (status, hostname). Then select the rolename column and unpivot the other columns. Split the merged column on the delimiter you used then apply the same transformation steps used in the first case.

Peter

 

[Satya123]

Many thanks Peter, this is exactly what I was looking for and power query worked perfectly fine. I used the data from the first case and did the pivot as you have suggested.

Thanks Again.

 

[Satya123]

Hi @peter789 is there a way I can sort this output also?? Like I want to sort the rows based on number of count of the keyword HELD
Total number of count of HELD for all hosts is 1 for a role then it should be no. 1
If total number of count of HELD for all hosts is 2 for a role then it should be no. 2 n so on...

Table like below

Rolename Server-A Server-B Server-C

Role-A HELD
Role-B HELD
Role-C HELD HELD
Role-D HELD HELD HELD

 

[peter789]

One way would be to create a second query connected to the original source data which is in an un-pivotted format. Filter status by HELD then just keep the Rolename and status columns. Group by rolename aggregate on count rows. Merge original query with this one joined on rolename. Expand table to add count.
Peter