Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Create a Table (as a measure?)

Hi Guys I an sure I must be missing something really simple... but to start me off, I was trying to create a Table, and at it's most basic, it refuses to work If i use VALUES alone,...
Need Help
  • HotChilli's avatar
    HotChilli
    3 years ago

    Unpivotting all the Period columns is the right way to do it. You then have the numeric values in one column so you can plot them.

    You maybe need to manipulate the Attribute column so that you can get the x-axis values to order correctly.  So probably create a calculated column or Power Query column to make a sortable value , for example 

    Period 03 2023 -> 2023/03 (fairly straightforward to do).

    If you want a Year column, you can extract that too.

    Bear in mind these are text values at the moment.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Yes, I think you are right (I would like to know what is wrong with the syntax, but that is not the main issue)

I have been working on the "usual" (or most recommended) approach of "Un-Pivot" to get the monthly columns into rows, but I fear that sets me up for more problems down the line as not only will the list be enormous, but as new columns are added in I predict issues! 😄 

I cannot seem to attach a file, which is strange, but instead let me try offering a table 
(eventually the PBI will be creating a report to assess hundreds of projects accross at least 4 years of monthly columns, with more columns appearing as new projects are won and created)



Project NumberPeriod 03 2023Period 04 2023Period 05 2023Period 06 2023Period 07 2023Period 08 2023Period 09 2023Period 10 2023Period 11 2023Period 12 2023Period 01 2024
PR.925494.010£ -450£ -450         
PR.925426.010£ -9,000£ 0£ 0        
PR.925502.010£ 0£ 0         
PR.925548.010£ -300£ -300  Â£ -300£ -300     
PR.925551.010£ -1,200          
PR.925612.010£ -6,329          
PR.925899.010£ -189£ -189£ -189        
PR.925899.900£ -896£ -896£ -896        
PR.925904.010£ -934          
PR.925929.010£ -920£ -920         
PR.925931.010£ -1,000£ -200         
PR.925934.010 Â£ -4,000         
PR.925936.800 Â£ -9,645         
PR.925992.010£ -1,809£ -1,809£ -1,809£ -1,809£ -1,809£ -1,809     
PR.926010.010£ -990£ -2,227£ -2,227£ -2,227£ -2,227£ -2,227£ -990£ -990£ -990£ -990£ -990
PR.926066.010 Â£ -9,500         
PR.926139.010£ -2,000£ -236         
PR.926299.010£ -392£ -392£ -392        
PR.926302.010£ -1,294£ -1,294£ -1,294£ -1,294       
PR.926610.010£ -288£ -288         
PR.926652.020£ -9,473£ -9,473         
PR.926654.010£ -150£ -150£ -150£ -150£ -1,000£ -1,000£ -150    
PR.926661.010£ -2,563£ -2,563         
PR.926863.010  Â£ -3,500        
PR.926893.010  Â£ -7,469£ -7,469       
PR.929008.010£ -2,400£ -2,400£ -2,400£ -2,400       
PR.929008.800£ -2,410          
PR.929009.010£ -8,466£ -8,466£ -3,956£ -1,663£ -4,334      
PR.929010.010£ -1,126£ -1,126£ -1,126£ -1,126£ -1,126£ -1,126£ -1,126£ -1,126£ -1,126£ -1,126