Power Query - Bring Max Value From Another Table

[Anonymous]

Hello,

Reaching out with a topic which beats me totally.

I have two tables:

1) Header Level Report - Contains invoice details on a header level

2) Invoice Status History Report - Contains all the steps through which the invoice went

I have as column element the invoice number.

At the end of table two I have some columns classifying the steps.

I want to achieve two things:

1) In the header level report to bring a column with the Group value which needed the most time for that specific invoice and the corresponding time.

2) Add two columns summing up separately the time spent for Posting & Hold.

Is something like this possible?

Thank you,

Cristian

[Nolock]

Hi @Anonymous,

yes, both requests are possible. But I can't prepare a PowerQuery query for you if you don't provider any test data. Even I don't know what column should be used for join.

What should be done:

Request Nr. 1: Use Table.NestedJoin in PQ or Merge Queries in the UI to join the header and the detail table. It creates a new column which contains all rows of detail table for the current key. Go to PQ Editor and filter this new column to get only one row containing the max value. Expand this row into the header table and you're done.

Request Nr. 2: Again, join those two tables and sum up columns in the new nested table.

Btw. you can do both steps at once which will increase the performance of the solution.