Why doesn't table connection for Excel pivot table work?

Jan Sijben 20 Reputation points
2023-02-01T16:00:45.3166667+00:00

Hello,

I'm creating a pivot table based on two Excel tables: Sales and Orders. The unique field is the OrderDetailNumber. The OrderDetailNumber exists only one time in both tables. It's origin is in the Orders table and it shows up (is put) in the Sales table after the OrderDetail has been invoiced. So it is in fact a one to one relationship, be it that the Orders table has more OrderDetailNumbers because there is a delay between registering the order and invoicing it.

My pivot table is intended to show order details for the orders that have been invoiced so I start with the Invoice table and look for the details in the Order table. The OrderDetailNumber is not the first column in the Order table but this shouldn't be an issue as far as I know. The OrderDetailNumbers in the Order table are sorted (in the Sales table they are not) and in both tables the OrderDetailNumber is text. The field header in the tables differ but that should not be an issue.

The connection between the tables was not automatically detected but was created. That gave no issues. Can anyone tell me what might be the issue?

Thank you.

Kind regards,

Jan

Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,495 questions
Accepted answer
  1. Emily Hua-MSFT 27,546 Reputation points
    2023-02-07T08:32:16.31+00:00

    Hi @Jan Sijben

    Sorry for the delay and thanks for your sharing.

    So you at least have to put one value in even if the information that you want the pivot table to provide doesn't have to contain a value?

    Yes, you need put fields under Values area for pivot table.

    If you have a chance to be back, could you please accept my reply as an answer? This action will help other users on Q&A who encounter with the same issue. Thanks for your understanding.

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Jan Sijben 20 Reputation points
    2023-02-02T12:06:31.07+00:00

    Hello Emily,

    I had prepared the screenshots but the issue is indeed solved by putting a field in the Values part of the Pivot table as you instructed in the last line of your answer.

    So you at least have to put one value in even if the information that you want the pivot table to provide doesn't have to contain a value? I didn't know about this requirement so thank you, thank you very much.

    Kind regards,

    Jan

    0 comments