Automate

I am attempting to create a query using Customer Sold-To, Ship-To data and associated freight rates.  The output will become a "Look Up" sheet in an Excel workbook.  The purpose of this sheet is to function as the source of a VLookup from a separate sheet in the workbook.  So, it is important that the output be in a specific order.  That required order is Sales Organization, Division, Customer Sold-To, Customer.  It's also important that the user is not required to manipulate or order this Look Up sheet once it's created.

Since Query is limited to a single column for sorting, I've attempted to create a single Sort Key column as a concatenation of the four fields or columns shown above.  The Transformation Rule for this is

               =CONCATENATE(B2, D2, A2, F2) and results are stored in column AA2

When the resulting spreadsheet is viewed the Sort Key value in column AA2 appears correct.  However, the column cannot be sorted.  Clicking on a cell in this column shows the formula contents (or instructions), rather than the actual resulting value of the formula.

I have not been able to find a way to capture the actual resulting value of the formula.  I've attempted to use Excel functions "=VALUE" and "TEXT" to no avail.

I've included a copy of the query.  Appreciate any suggestions you can offer for how this can be accomplished.

I am attempting to create a query using Customer Sold-To, Ship-To data and associated freight rates.  The output will become a "Look Up" sheet in an Excel workbook.  The purpose of this sheet is to function as the source of a VLookup from a separate sheet in the workbook.  So, it is important that the output be in a specific order.  That required order is Sales Organization, Division, Customer Sold-To, Customer.  It's also important that the user is not required to manipulate or order this Look Up sheet once it's created.

Since Query is limited to a single column for sorting, I've attempted to create a single Sort Key column as a concatenation of the four fields or columns shown above.  The Transformation Rule for this is

               =CONCATENATE(B2, D2, A2, F2) and results are stored in column AA2

When the resulting spreadsheet is viewed the Sort Key value in column AA2 appears correct.  However, the column cannot be sorted.  Clicking on a cell in this column shows the formula contents (or instructions), rather than the actual resulting value of the formula.

I have not been able to find a way to capture the actual resulting value of the formula.  I've attempted to use Excel functions "=VALUE" and "TEXT" to no avail.

I've included a copy of the query.  Appreciate any suggestions you can offer for how this can be accomplished.

I am attempting to create a query using Customer Sold-To, Ship-To data and associated freight rates.  The output will become a "Look Up" sheet in an Excel workbook.  The purpose of this sheet is to function as the source of a VLookup from a separate sheet in the workbook.  So, it is important that the output be in a specific order.  That required order is Sales Organization, Division, Customer Sold-To, Customer.  It's also important that the user is not required to manipulate or order this Look Up sheet once it's created.

Since Query is limited to a single column for sorting, I've attempted to create a single Sort Key column as a concatenation of the four fields or columns shown above.  The Transformation Rule for this is

               =CONCATENATE(B2, D2, A2, F2) and results are stored in column AA2

When the resulting spreadsheet is viewed the Sort Key value in column AA2 appears correct.  However, the column cannot be sorted.  Clicking on a cell in this column shows the formula contents (or instructions), rather than the actual resulting value of the formula.

I have not been able to find a way to capture the actual resulting value of the formula.  I've attempted to use Excel functions "=VALUE" and "TEXT" to no avail.

I've included a copy of the query.  Appreciate any suggestions you can offer for how this can be accomplished.

I am attempting to create a query using Customer Sold-To, Ship-To data and associated freight rates.  The output will become a "Look Up" sheet in an Excel workbook.  The purpose of this sheet is to function as the source of a VLookup from a separate sheet in the workbook.  So, it is important that the output be in a specific order.  That required order is Sales Organization, Division, Customer Sold-To, Customer.  It's also important that the user is not required to manipulate or order this Look Up sheet once it's created.

Since Query is limited to a single column for sorting, I've attempted to create a single Sort Key column as a concatenation of the four fields or columns shown above.  The Transformation Rule for this is

               =CONCATENATE(B2, D2, A2, F2) and results are stored in column AA2

When the resulting spreadsheet is viewed the Sort Key value in column AA2 appears correct.  However, the column cannot be sorted.  Clicking on a cell in this column shows the formula contents (or instructions), rather than the actual resulting value of the formula.

I have not been able to find a way to capture the actual resulting value of the formula.  I've attempted to use Excel functions "=VALUE" and "TEXT" to no avail.

I've included a copy of the query.  Appreciate any suggestions you can offer for how this can be accomplished.

Sorry, I'm not able to share the file itself but here are relevant screenshots

Workspace

Criteria

Mapping

Transform rule

After publishing to excel, running and adding filters to the top row it's possible to sort

I am attempting to create a query using Customer Sold-To, Ship-To data and associated freight rates.  The output will become a "Look Up" sheet in an Excel workbook.  The purpose of this sheet is to function as the source of a VLookup from a separate sheet in the workbook.  So, it is important that the output be in a specific order.  That required order is Sales Organization, Division, Customer Sold-To, Customer.  It's also important that the user is not required to manipulate or order this Look Up sheet once it's created.

Since Query is limited to a single column for sorting, I've attempted to create a single Sort Key column as a concatenation of the four fields or columns shown above.  The Transformation Rule for this is

               =CONCATENATE(B2, D2, A2, F2) and results are stored in column AA2

When the resulting spreadsheet is viewed the Sort Key value in column AA2 appears correct.  However, the column cannot be sorted.  Clicking on a cell in this column shows the formula contents (or instructions), rather than the actual resulting value of the formula.

I have not been able to find a way to capture the actual resulting value of the formula.  I've attempted to use Excel functions "=VALUE" and "TEXT" to no avail.

I've included a copy of the query.  Appreciate any suggestions you can offer for how this can be accomplished.

Sorry, I'm not able to share the file itself but here are relevant screenshots

Workspace

Criteria

Mapping

Transform rule

After publishing to excel, running and adding filters to the top row it's possible to sort

I am attempting to create a query using Customer Sold-To, Ship-To data and associated freight rates.  The output will become a "Look Up" sheet in an Excel workbook.  The purpose of this sheet is to function as the source of a VLookup from a separate sheet in the workbook.  So, it is important that the output be in a specific order.  That required order is Sales Organization, Division, Customer Sold-To, Customer.  It's also important that the user is not required to manipulate or order this Look Up sheet once it's created.

Since Query is limited to a single column for sorting, I've attempted to create a single Sort Key column as a concatenation of the four fields or columns shown above.  The Transformation Rule for this is

               =CONCATENATE(B2, D2, A2, F2) and results are stored in column AA2

When the resulting spreadsheet is viewed the Sort Key value in column AA2 appears correct.  However, the column cannot be sorted.  Clicking on a cell in this column shows the formula contents (or instructions), rather than the actual resulting value of the formula.

I have not been able to find a way to capture the actual resulting value of the formula.  I've attempted to use Excel functions "=VALUE" and "TEXT" to no avail.

I've included a copy of the query.  Appreciate any suggestions you can offer for how this can be accomplished.