Create a Table (as a measure?)

[LinVen]

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, I get a column of the values found, If i try to get a total for the period (column) it says no

The long term goal here is to try to get my columns of raw data into a useable format, to plot the year in a Visual such as a Line Chart

If i go back and create the table from the query - so as a new query and remove extra columns, that works, but still struggling to plot the data the way i want... 

any ideas or tips anyone can offer?

[HotChilli · Accepted solution]

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.

[HotChilli]

What is the desired result from that data?

[LinVen]

Something along the lines of:

Which would be linked to the other visuals etc and controlled by the slicer - so you select the right project manager or project and you get an updated visual of the cost profile based on the seleection

[LinVen]

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 Number	Period 03 2023	Period 04 2023	Period 05 2023	Period 06 2023	Period 07 2023	Period 08 2023	Period 09 2023	Period 10 2023	Period 11 2023	Period 12 2023	Period 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

[HotChilli]

There seem to be a couple of challenges here

1. The syntax that has been written doesn't work but, even if it did, it doesn't help you plot the data

2. The desire to plot your data in a useful way.

--

I suggest ignoring (1) and concentrating on (2).

If you provide a small sample of data and show your desired result, someone will help.