Combine header when have value in single row

[alvin199]

Hi, 

This is the sample data. 

I want to visual it into bar chat. My initial plan (this is what I think is possible) is to create new column for header with value in a single row. The 1 is just an indication of that single row is having that type of fruit so no aggregation on the 1.

Here my expected output

What operations should I do to get the extra column? 

[v-yangliu-msft]

Hi  @alvin199 ,

Here are the steps you can follow：

1. Enter the power query, select [apple],[orange],[grape],[lemon],[startftuit],[pearl], right-click—Replace Values.

Replace blank values with 0.

2. Select [apple],[orange],[grape],[lemon],[startftuit],[pearl] and click Unpivot Columns.

Result:

3. Create calculated column.

CON =
IF(
    'Table2'[Value]=1,
CONCATENATEX(FILTER(ALL(Table2),[Name]=EARLIER([Name])&&'Table2'[Value]=1),[Attribute],","),[Attribute] )

Flag =
var _1=COUNTx(FILTER(ALL(Table2),'Table2'[Name]=EARLIER('Table2'[Name])&&'Table2'[Value]=1),[Value])
var _if1=IF(
    [Value]=1,_1,0)
return
IF(
    AND(_if1=1,[Value]=1) || _if1=0,_if1,_if1-1)

4. Create measure.

Measure =
MAX('Table2'[Flag])

5. Result:

Best Regards,

Liu Yang

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

[Kumail]

Hello @alvin199 

If you could send a sample .pbix that demonstrates what you are looking to get, it would really help provide you with a quick solution.

You can send the sample .pbix file by adding it to your drive or dropbox and add the link here.

Regards

Kumail Raza

[alvin199]

Here is the sample of the data.

https://docs.google.com/spreadsheets/d/1UzxRODfpRKypbKNEk2xG2qEsHWFV3HmK/edit?usp=sharing&ouid=11028...

The column needs to combine header name in new column is from column D to J. 

[Kumail]

Hello @alvin199 

If I have understood your requirement clearly, then this visual willl answer your query.

Regards

Kumail Raza

Did this help? Kudos are appreciated!

Consider Accept it as the solution to help the other members find it more quickly

[alvin199]

Hi @Kumail 

Thank you for your reply.

Can you share the PBIX file for me to know how it works?

Thanks.

[Kumail]

Hello @alvin199 

https://drive.google.com/file/d/1Ypu3Av8v71i9ilBZvG5kgfppSYje-BT0/view?usp=sharing

Regards

Kumail Raza

Did this help? Kudos are appreciated!

Consider Accept it as the solution to help the other members find it more quickly

[alvin199]

Hi @Kumail 

The y-axis should be the count of Joiners while the x-axis is the Attribute (Coding, Art and etc.).

The difficult part is to handle each joiner will have 1 or more than 1 attribute. 

[alvin199]

@Kumail 

I have request access permission. Kindly allow. 😀

[Kumail]

@alvin199 access already given the same day!

[alvin199]

Yup, I have look into your suggested work but it is not what I am expected. 

The y-axis should be the count of Joiners while the x-axis is the Attribute (Coding, Art and etc.).

The difficult part is to handle each joiner will have 1 or more than 1 attribute. 

[amitchandak]

@alvin199 , With this model better to create additional columns

[alvin199]

Yes create additional columns as for having more than 1 column in a single row. 
However, how to identify the 1 and then combine the column header for those having more than 1 "1" in individual column?