Excel Power Query - Sorting/Filtering Table

0
votes

I am using Power Query to pull data from a SQL database into Excel as a simple table.

The intent of this is to record comments in a manual column (not part of the SQL database) for the various records pulled into the table, and then refresh the table week-over-week to get updated data for each record but maintain the comments from previous weeks.

I was able to successfully write the query and retrieve the data, but whenever I sort the data and then refresh the query, my manually added comments become misaligned.

I have checked the "Preserve column sort/filter/layout" option on the External Data Properties window for the query, and filtering seems to work fine (if I only filter and add manual comments, refreshing the dataset does not alter the placement of the comments) and yet sorting does not work.

When the SQL query is run, the records are sorted by a key column (wherein the value for each record is unique), but I cannot find a way in the Power Query editor to force the refreshed data to align to this field.

Example per Wedge's Response:

Step 1. Execute SQL Query SQL Data Query

Step 2. Add manual comment Manual Comment

Step 3. Sort differently than original query (not same as ORDER BY) Sort Differently

Step 4. Refresh the original SQL Query SQL Query Refresh

As you can see in the example, the "Test" comment is aligned to a different PurchaseOrderLineID after I refreshed the SQL Query.

I have attempted to create a "From Table" query (from my SQL output table) and Merge that with my original SQL query as seen below, but I am still having the same issue. Merge Query

1
sqlexcelsortingfilteringpowerquery

1 Answers

1
votes

If the rows you are importing all have a way to uniquely identify them (which coming out of a SQL table I would think this is the case) then you can use that to keep your comments aligned. Go to your output table in Excel, use "From Table" on the PQ menu, and make it a connection only Query.

Then go to your original Query that is pulling from the SQL database and add a "Merge Queries" step against the table you just loaded in (which will have your comment column). Merge them based on whatever uniquely identifies the rows and expand only the comment column from the merge.

This way whenever you refresh your table, it will match and retain any existing comments before updating the output table in Excel.