Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
LinVen
Helper I
Helper I

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

LinVen_0-1678823055318.png



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?

1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

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.

View solution in original post

5 REPLIES 5
HotChilli
Super User
Super User

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
Super User
Super User

What is the desired result from that data?

Something along the lines of:

LinVen_0-1678891664201.png



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
Helper I
Helper I

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 
HotChilli
Super User
Super User

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors