Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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
@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
Hi @Anonymous
Please share some data and the visual expected in onedrive / dropbox and post the link.\