Forum Discussion
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, 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?
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.
5 Replies
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.
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.
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 What is the desired result from that data?