Forum Discussion

alvin199's avatar
alvin199
Icon for Helper III rankHelper III
4 years ago

Combine header when have value in single row

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? 

Need Help

12 Replies

    • alvin199's avatar
      alvin199
      Icon for Helper III rankHelper III
      4 years ago

      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?

  • Kumail's avatar
    Kumail
    Icon for Impactful Individual rankImpactful Individual
    4 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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