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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.