Forum Discussion
Compare queries for data lacking time stamps
I'm dealing with data from a 20 years old Oracle dabase that's got no timestamps and no indexing.
When querying, I would like to be able to compare the content of the query with that of the previous query, and see changes--and if possible, even mark the rows that changed with the latest query date.
Is this possible with Power Query alone? And if so how?
6 Replies
Sure, you can use Marge Queries as below.
Then
- Select Both queries you wish to compare.
- Choose a set of columns that have the desired link between them.
- Pick king of join "full outer" for example if you want to compare common and missing records on both ends.
The below link will explain the process in more detail.
https://www.excelguru.ca/blog/2015/10/28/merge-data-based-on-two-columns/
Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
Hi,
Thanks, but I'm not quite sure to understand how merging queries will help me tag rows of the newest queries that has changed.
So imagine the value in the SECOND row below colum TOQUARTER was 3 yesterday, and today it is 4. There is no way for me currently to notice this as a "LastUpdate" timestamp is missing on the row. Secondly the row is not indexed, so I the assumption is the data is queries in the same order today as it was yesterday.
How can I notice the change using power query, and applying a timestamp of today (query time) on the second row, to mark the change?
APPTYPE ACADEMICYEAR QUARTER MODULE APPCODE PROGCODE PREFIXCODE FNAME MNAME LNAME FNAME2 MNAME2 LNAME2 STDCODE UNIVERCODE STATUS APPSTAT DDATE RESONCODE TOACADEMICYEAR TOQUARTER TOMODULE A 2019 1 1 62S0020 02 001 John - Doe - - - S620020 - - E - 4 2020 4 2 A 2019 1 1 62S0021 02 001 Mary - Jenkins - - - S620050 - - E - 4 2020 3 1 A 2019 1 1 62S0022 02 001 Alfonzo Leonardo Leppe - - - - - - E - 5 2019 2 2 A 2019 1 1 62S0024 02 001 Cheng - Zhao - - - - - - E - 4 2020 3 1 A 2019 1 1 62S0026 02 002 Suman - Mhuammad - - - S620030 - - E - 4 2019 4 2 A 2019 1 1 62S0027 02 001 Tang - Duong - - - S620032 - - E - 7 2020 3 1 A 2019 1 1 62S0029 02 001 Warren - Spade - - - S620031 - - E - 4 2020 2 1 A 2019 1 1 62S0030 02 001 Gwendolin - Flendich - - - S620019 - - E - 4 2020 1 2 If you are compering change overnight you can.
- if you have scheduled job to update this table, adjust the stored procedure to check for changes and time stamp the records in oracle db.
- You can create a data flow in power bi service and take a daily snapshot of your table and later compare it in Query editor.
Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
The short answer is yes however if you are looking for a solution please can you create a sample of data that you wish to compare.
Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.Sure thanks!
Here is a sample data from one of many such tables (names have been altered and any resemblence with real names is pure coincidence):
APPTYPE ACADEMICYEAR QUARTER MODULE APPCODE PROGCODE PREFIXCODE FNAME MNAME LNAME FNAME2 MNAME2 LNAME2 STDCODE UNIVERCODE STATUS APPSTAT DDATE RESONCODE TOACADEMICYEAR TOQUARTER TOMODULE A 2019 1 1 62S0020 02 001 John - Doe - - - S620020 - - E - 4 2020 4 2 A 2019 1 1 62S0021 02 001 Mary - Jenkins - - - S620050 - - E - 4 2020 3 1 A 2019 1 1 62S0022 02 001 Alfonzo Leonardo Leppe - - - - - - E - 5 2019 2 2 A 2019 1 1 62S0024 02 001 Cheng - Zhao - - - - - - E - 4 2020 3 1 A 2019 1 1 62S0026 02 002 Suman - Mhuammad - - - S620030 - - E - 4 2019 4 2 A 2019 1 1 62S0027 02 001 Tang - Duong - - - S620032 - - E - 7 2020 3 1 A 2019 1 1 62S0029 02 001 Warren - Spade - - - S620031 - - E - 4 2020 2 1 A 2019 1 1 62S0030 02 001 Gwendolin - Flendich - - - S620019 - - E - 4 2020 1 2
