Power Query

Resolved · High Priority · Version 2010

Mon 15 Jan 2018, 16:12 Delegate Adam said...

Adam has attended:
Excel PowerPivot course

Power Query

Hello,

Is it possible to re-order rows in a pivot table that has been created from data collated using power query?

Also, is it possible to perform calculations on data that is being drawn via power query so that these numbers appear in the pivot table?

Many thanks,

Wed 17 Jan 2018, 15:51 Trainer Sarah said...

RE: Power Query

Hi Adam

Thanks for your question, sorry for the slow reply but all of our PowerPvot trainers have been extremely busy training all week so far.

I've passed on the message and one of them will be get back to you as soon as they have a chance to look at this.

Regards,

Sarah
Microsoft Trainer

Fri 19 Jan 2018, 09:35 Trainer Jens said...

RE: Power Query

Hi Adam,

Thank you for the forum question.

You have two options to change the default sorting in PivotTables (ascending or descending).

You can change the order of the rows by click and drag or you can setup a custom sorting list. To setup a custom sorting list please follow the hyperlink below.

https://www.extendoffice.com/documents/excel/2025-excel-pivot-table-sort-custom-list.html

Yes you can perform calculations in Power Query you can show in PivotTables, but unfortunately you cannot use the DAX knowledge from PowerPivot or SQL. Microsoft has created another language for Power Query.

If you follow the hyperlink below you can find some examples.

https://blog.crossjoin.co.uk/2013/10/18/implementing-common-calculations-in-power-query/

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Mon 22 Jan 2018, 16:55 Trainer Jens said...

Will be marked as resolved in 5 days

Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.

Mon 29 Jan 2018: Automatically marked as resolved.

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Bracketed negative numbers

Often Excel users wish to display negative numbers in colour red and bracketed

Intstructions
Step1. Select Format > Cells menu options. Within Numbers tabsheet, select Category = Custom.
Step 2. Select a type such as #,##0;[Red]-#,##0;; that specifies a colour in square brackets.
Step 3. Amend as follows; #,##0;[Red](#,##0;;

Notes: Excel formatting featues are of the form
"Positive; Negative;Zero;Text" separated by semicolon.

View all Excel hints and tips