Comparison between last week status and this week status of column from two different tables

[Anonymous]

Hi

I have requirement that, i have column which contains Employee billing status i need to compare how many employees's status is changed from billing to bench from last week to this week. For last week;s data i am referreing excel sheet, for this week's data i am referring to db table how to compare and get the count of the employees whose status is changed,and both tables(excel and db table) contains duplicates

Thanks in advance

[CheenuSing]

Hi @Anonymous

One thought that occurs to me is to create a powerquery step that is executed before all other power query steps during refresh.

This query step should create the summary of information from the fact table grouped by which ever dimension

At the end of the queries refresh create the same summary of information from the  updated fact table grouped by which ever dimension.

Compare these two tables.

As a start you may keep the first step table same as the current fact data.

Share your thoughts.

Cheers

CheenuSing

[Eric_Zhang · Accepted solution]

@Anonymous

Not sure I've got your idea 100%, check

let
    LastWeekData= Table.FromRows({{"John","2017-03-05","billing"},{"Jack","2017-03-05","billing"},{"Jerry","2017-03-05","bench"}},{"empId","date","status"}),
    ThisWeekData= Table.FromRows({{"John","2017-03-12","bench"},{"Jack","2017-03-12","bench"},{"Jerry","2017-03-12","billing"},{"Jessie","2017-03-12","bench"}},{"empId","date","status"}),
    LastWeekWhosBilling = Table.SelectRows(LastWeekData, each [status] = "billing"),
    ThisWeekDataWhosBench = Table.SelectRows(ThisWeekData, each [status] = "bench"),
    requiredData = Table.NestedJoin(ThisWeekDataWhosBench ,{"empId"},LastWeekWhosBilling ,{"empId"},"table",JoinKind.Inner)

in
    requiredData

[CheenuSing]

Hi @Anonymous

Please share some data and the visual expected in onedrive / dropbox and post the link.\