To do this, we must get the key column in both tables that will contain all columns concatenated in a single key column. This will give us the rows from the NewData table that will later be loaded into the sheet so that with every new refresh, we see only new changes. Our solution consists of merging these two tables and using the Left Anti join. We’ll start by getting both of our tables inside the Power Query editor. Also, we want to make our solution as dynamic as possible, so that if you rename a column or add a new one, the query does not fail to load. The solution consists of using Power Query to compare tables and load the new table to the sheet to see which changes occurred in the process. The idea is that we can see any change we make in the NewData table without having to scan the whole table row by row and comparing it with the old table. For that, we will copy the table and create a new one called NewData. The problemĭuring the year, we might want to make changes in this table (for example change status or plan). To demonstrate how this can be achieved, we will use a simplified example with a table containing 4 columns and 4 rows. We might make changes to this table, and we want to see them all in one place compared to the original (history) table. For example, we have a table with products containing status and planned sales. Then we have a new table which has the same structure as the first one, but here we can make changes ( NewData).
The presumption is that we have one table that serves as a database ( HistoryData). In this article, we will show you the way to track changes you make in your table by using Power Query. In case you wish to learn how it’s done, check the remaining of the article. The only prerequisite is that both old and new tables have the same column names. We have created an M script in Excel that uses the left anti join to check for data inconsistency. The issue is that i had to change all the names from camel cases to upper cases for power queries names, so in this case it is "HanaTable" and should be replaced to "HANATable".If you ever had a need to check if anything changed in the new table compared to the previous export, no matter in which column, then this is the article for you. WorkbookConnection.RefreshWithRefreshAll = False With ActiveSheet.ListObjects(1).QueryTable CommandText = Array("SELECT * FROM " & "PQ" & QName)
"OLEDB Provider=.1 Data Source=$Workbook$ Location=" & "PQ" & QName & " Extended Properties=""""" _
0 kommentar(er)
