Forum Discussion

NB3's avatar
NB3
Helper III
7 years ago
Solved

Split multiple rows and columns

Hi everyone,

 

I need to create 2 different charts based on the following table (on with the year and one with YTD).

 

In the document it is from, year and YTD are mixed together ... Is there any way I can have a table with the year and another with YTD (and the dept, associated year and value attached to it) ?

 

And, if someone makes changes in the original document, is it going to refresh afterwards when neeeded ?

 

Thanks for the help,

 

 

 

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    7 years ago

    NB3

     

    I will create two tables for this. Please check if this can meet your requirements. Thanks

     

     

    TABLE2 = FILTER('Sheet27',SEARCH("YTD",'Sheet27'[Year],1,0)=0)

     

    TABLE3 = FILTER('Sheet27',SEARCH("YTD",'Sheet27'[Year],1,0)>0)

     

5 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    7 years ago

    NB3

     

    I will create two tables for this. Please check if this can meet your requirements. Thanks

     

     

    TABLE2 = FILTER('Sheet27',SEARCH("YTD",'Sheet27'[Year],1,0)=0)

     

    TABLE3 = FILTER('Sheet27',SEARCH("YTD",'Sheet27'[Year],1,0)>0)

     

    • NB3's avatar
      NB3
      Helper III
      7 years ago

      ryan_mayu

       

      That's exactly what I need

       

      Though I get this message when I try to split in 2 different tables :

       

       

      Any idea why ?

       

      Thanks a lot,

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi NB3,

         

        I think you are use these formula in calculated column, right? if this is a case, ryan_mayu's formula are calculated table formulas, please click on 'new table' icon to create new table to use his formulas.

         

        Regards,

        Xiaoxin Sheng