Forum Discussion

RonaldvdH's avatar
RonaldvdH
Post Patron
4 years ago
Solved

Columns with blank value

Goodday,   I have a matrix with all types of materials and not all materials are used in every project. Basically i only want to show the colums (when i use the slicer) that have values > 0   I ...
Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    4 years ago

    Hi RonaldvdH ,

    You need to unpivot the columns（you could create a copy table then do this) :

    = Table.UnpivotOtherColumns(#"Changed Type", {"Hoofdproject", "AP gebied naam", "2V_DAC", "12V_MDAC", "24v_LTMC", "48V_LTMC", "96V_LTMC", "192V_LTMC", "LM_EDSA-D4-48", "Woningen", "14/10 connectoren", "Markers", "14/10 eind connectoren"}, "Attribute", "Value")

      Click Ctrl + the cloumn you want to use：

    Then apply and create visual:

     

    Refer:

    https://support.microsoft.com/en-us/office/unpivot-columns-power-query-0f7bad4b-9ea1-49c1-9d95-f588221c7098 

     

    Did I answer your question? Mark my post as a solution!


    Best Regards

    Lucien

v-luwang-msft's avatar
v-luwang-msft
Community Support
4 years ago

HI RonaldvdH ，

Could you pls share your pbix file ?And remember to remove confidential data.

I checked  my measure provided.And try to add new data as your table .

measure:

Measure 2 = IF(MAX(Table2[value])=0,BLANK(),max(Table2[value]))

 

And in visual ,it still work:(type3 is hidden)

 

 

 

 

Best Regards

Lucien

RonaldvdH's avatar
RonaldvdH
Post Patron
4 years ago

v-luwang-msft your formula says that if Table2[value]=0 then return BLANK() but the value is already Blank() because in the excelfile there are no values (empty cell) so that is why I changed the formula to IF(table2[value]=BLANK();BLANK() ....

 

However is still doesn't work 😉

 

Can i E-mail you the file ?

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    4 years ago

    Hi RonaldvdH ，

    You could share me by a public link. And remember to remove confidential data.

     

    Best Regards

    Lucien

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi v-luwang-msft ,
      i think i got a similiar problem. I'd like to color the empty cells red but it just won't do it.
      It seems like it skips the empty cells or doesn't even recognize that they exist?
      For example:

      var _value = SELECTEDVALUE(table[value])
return IF(ISBLANK(_value), "red", "white")

      Help

      Best Regards
      Jakob