PQ Difference between two tables

Power Query
1

Hi All,

I have been inspired by the video from Melissa which compares two tables and produces a table with the differences. I have been trying to implement this at work however I only want to see the variance of the changed amount and any new or missing amounts

For example,
Last month value for “Mobile” was 150 and this month is 200. The query will give the answer 200 however I would like to see just the variance for “Mobile” of 50.

Any ideas how to achieve this?

This is the result I would like:

image
image1237×536 22.4 KB

This is a very simple example, at work this file is 50,000 lines long.

Thanks in advance and file is attached
CompareCurrentMthPriorMth.pbix (27.7 KB)

2

Hi @KimC,

See what works best for you, a LeftOuterJoin on the Source

let
    Source = Table.NestedJoin(CurrentMth, {"Country", "Customer", "Customer ID", "FiscalYrMth"}, PriorMth, {"Country", "Customer", "Customer ID", "FiscalYrMth"}, "Change", JoinKind.LeftOuter),
    ReplaceValues = Table.ReplaceValue(Source, each [Change], each if [Change]{0}[Amount] = null then [Amount] else [Amount] - [Change]{0}[Amount],Replacer.ReplaceValue,{"Change"}),
    SelectRows = Table.SelectRows(ReplaceValues, each ([Change] <> 0))
in
    SelectRows

.
or a LeftOuterJoin on the Result (I expect this one but let me know, thanks)

let
    Source = CompareMths,
    LeftOuterJoin = Table.NestedJoin(Source, {"Country", "Customer", "Customer ID", "FiscalYrMth"}, PriorMth, {"Country", "Customer", "Customer ID", "FiscalYrMth"}, "Change", JoinKind.LeftOuter),
    ReplaceValues = Table.ReplaceValue(LeftOuterJoin, each [Change], each if [Change]{0}[Amount] = null then [Amount] else [Amount] - [Change]{0}[Amount],Replacer.ReplaceValue,{"Change"})
in
    ReplaceValues

Just a quick note as your sample only had one possible matching row, this method works but if you could have multiple matching rows you’ll need a different approach…

I hope this is helpful

3

@Melissa, i have only one word…“Amazing”

Both solutions work on this dataset. I will now go check it out on my work data. Thank you so much :pray: :pray: :pray:

1 Like
4

Hello @KimC! It’s great to know that you are making progress with your query :slight_smile:

Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’.

We request you to kindly take time to answer the Enterprise DNA Forum User Experience Survey, We hope you’ll give your insights on how we can further improve the Support forum. Thanks!