×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Pivot Table - Calculated Fields / Different From

Pivot Table - Calculated Fields / Different From

Pivot Table - Calculated Fields / Different From

(OP)
Hi
Needing some help with Pivot Table calculated fields or suggested solution.

Attached is a worksheet with data and pivot table.

I have entered the expected outcome required below the pivot table.
As i am not sure how to get the pivot to calculate what i want, I have hence put in formulas.

Ideally, I need to learn how to get the pivot table to calculate different scenarios.

Thanks,
arv

RE: Pivot Table - Calculated Fields / Different From

At some level of complexity of the problem it is easier to switch to another excel tool (power query here). The source table is processed in power query to get previous month and previous year data. I did all the calculations in two queries and next use one as a source for pivot table.
There could be still some work to done, I hard-coded 2022 in query, dates could be used to have them in pivot table. Some of actions are pure recording outputs, some are written by me (as deducting two columns, however this can be done in three recorded steps: add new column, delete old one, rename new one).
The M-1 January data is different in my file, your formulas refer to Dec. 2022 mine to Dec. 2021.

combo

RE: Pivot Table - Calculated Fields / Different From

(OP)
Hi combo
Thanks for your reply and also providing the sample solution in the file.

I'm not familiar with the below. Do you mind letting me know what each of these steps are aiming to do?
The tSourced appears to be adding columns but not sure of the objective of the formula.

tSource
= Table.AddColumn(#"Added Custom2", "ID", each 12*([YearID]-2021)+[MonthID])
= Table.AddColumn(#"Added Custom3", "ID_01", each [ID]-1)
= Table.AddColumn(#"Added Custom4", "ID_12", each [ID]-12)

qOutput
= Table.ReplaceValue(#"Removed Columns", each [Qty_01], each [Qty] - [Qty_01],Replacer.ReplaceValue, {"Qty_01"})
= Table.ReplaceValue(Calc_01, each [Qty_12], each [Qty] - [Qty_12],Replacer.ReplaceValue, {"Qty_12"})

Thanks,
arv

RE: Pivot Table - Calculated Fields / Different From

It's M language of power query, like in VBA, some actions can be recorded, but recorded code is only a part of the power of language.

Each #"Added Custom3" or Calc_01 (depending on formula name, if space inside, #"..." around are required) refers to prevoius step in query:
= Table.AddColumn(#"Added Custom2", "ID", each 12*([YearID]-2021)+[MonthID]) adds to previous step column "ID" with contents [YearID]-2021, here 0 or 1,
= Table.AddColumn(#"Added Custom3", "ID_01", each [ID]-1) adds column "ID_01" with [ID]-1, i.e. previous month,
= Table.AddColumn(#"Added Custom4", "ID_12", each [ID]-12) adds column "ID_12", ID of the same month year ago.
This is preprocessing in first query (tSource), those indexes will be used for linking to proper past months data.

qOutput query:
= Table.ReplaceValue(#"Removed Columns", each [Qty_01], each [Qty] - [Qty_01],Replacer.ReplaceValue, {"Qty_01"})
= Table.ReplaceValue(Calc_01, each [Qty_12], each [Qty] - [Qty_12],Replacer.ReplaceValue, {"Qty_12"})
respectively in steps "Removed Columns" and Calc_01, calculates M-M and Y-Y differences. It is an one line not recordable code. Instead, it is possible to (1) add new column and calculate difference, (2) delete unused column, and (3) rename column (not possible to have two Qty_01 columns) - three steps.

combo

RE: Pivot Table - Calculated Fields / Different From

(OP)
Hi Combo
Thanks for the explanation.

Say what about if Apples / Class A is in Jan-21 but not in Jan-22 (and vice-versa), the pivot table is not picking up anything.

Thanks,
arv

RE: Pivot Table - Calculated Fields / Different From

(OP)
HI Combo
When merging the tables, say if i have more columns in the data table, do i have to select all the columns from both tables?
Sorry as i am not very familiar with joining tables.

Thanks,
arv

RE: Pivot Table - Calculated Fields / Different From

1.
You have exact dates in your table. A solution may be a custom column with date with day 1 and month and year from date. Alternatively index from month and year. In both solutions the original column with date should (in PQ) be deleted before grouping.
2.
When two tables are joined in power query, a column is added with records that match join criteria (fields and type of join), technically there is a table in each cell. A button near the new column name allows to expand these tables and select required field. In existing set of commands of pover query some entries have icons on the right, clicking them allows to customize the step, it affect data in next steps.

combo

RE: Pivot Table - Calculated Fields / Different From

(OP)
HI Combo
Thanks for the update.
Noted re point 2. I will give it a ahot.

Not 100% sure if i understand point 1 but I'll try.

Thanks,
arv

RE: Pivot Table - Calculated Fields / Different From

(OP)
Hi Combo
Re Point 1, i'll need some help again.

Added another row and call it Jackfruit/Class A with period Jan-22.
I intentionally omitted Jackfruit / Class A from period Jan-21.

With the merge, it will not be able to match the record (Jackfruit/Class A).

What's the best way to overcome this?

Thanks,
arv

RE: Pivot Table - Calculated Fields / Different From

After merging tables with past (M-1, M-12') data and expanding columns, Qty_01 and Qty_12 have nulls, they have to be replaced by 0 for future calculations, otherwise nulls are returned. I added a step 'Replaced Value' after 'Removed Columns' in 'qOutput' and qOutput (2)' queries:
= Table.ReplaceValue(#"Removed Columns",null,0,Replacer.ReplaceValue,{"Qty_01", "Qty_12"})
(a result from recorder after selecting 'Removed columns' step, selecting fields 'Qty_01' and 'Qty_12', and "replace values' action from menu - replace 'null' by '0')

The full code (advanced editor):
let
    Source = tSource,
    #"Filtered Rows" = Table.SelectRows(Source, each ([YearID] = 2022)),
    #"Merged Queries" = Table.NestedJoin(#"Filtered Rows", {"Product", "Class", "ID_01"}, tSource, {"Product", "Class", "ID"}, "tSource", JoinKind.LeftOuter),
    #"Expanded {0}" = Table.ExpandTableColumn(#"Merged Queries", "tSource", {"Qty"}, {"Qty_01"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded {0}", {"Product", "Class", "ID_12"}, tSource, {"Product", "Class", "ID"}, "tSource", JoinKind.LeftOuter),
    #"Expanded {0}1" = Table.ExpandTableColumn(#"Merged Queries1", "tSource", {"Qty"}, {"Qty_12"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded {0}1",{"YearID", "ID", "ID_01", "ID_12"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns",null,0,Replacer.ReplaceValue,{"Qty_01", "Qty_12"}),
    Calc_01 = Table.ReplaceValue(#"Replaced Value", each [Qty_01], each [Qty] - [Qty_01],Replacer.ReplaceValue, {"Qty_01"}),
    Calc_12 = Table.ReplaceValue(Calc_01, each [Qty_12], each [Qty] - [Qty_12],Replacer.ReplaceValue, {"Qty_12"})
in
    Calc_12 

Note that:
- excel may automatically change connection name when the query changes,
- pivot table duplicates query and uses the new connection, su it is necessary to change the query for pivot table too,
- power query has a simple pivot functionality, you can consider building output tables in power query and return results in tables (in a copy of qOutput, a step (Removed Columns1) = Table.RemoveColumns(Calc_12,{"Qty", "MonthID", "Qty_12"}) and next (Pivoted Column) = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Month]), "Month", "Qty_01", List.Sum) loaded to worksheet returns a table close to pivot table summary for 'Qty_01').

combo

RE: Pivot Table - Calculated Fields / Different From

(OP)
Hi Combo
The pivot table lists all the fruits that is listed in 2022.
However, if there is a mismatch whereby a type of fruit that is in 2021 but not in 2022, the pivot table will not list the item.

Thanks,
arv

RE: Pivot Table - Calculated Fields / Different From

You may consider to rebuild queries for more robust structure:
1. create a list 1 .. 12 of months to have all months even if some data is missing, a query with:
let
    Source = {1..12},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table" 
The same for {"A" .. "B"} list
2. have a full list of fruits, remove duplicates in query, from single column table: = Table.Distinct( )
3. combine tables, to have full set of row/column headers,
4. merge M-1 and M-12 data.

To handle nulls, you can either replace 'null' by 0 or add conditional column.

Welcome to Power Query worldtiphat. When you experiment, frequently save the query and workbook, sometimes wrong query can hang or close excel.

combo

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close