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...
Need Help
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