Hi Tykru,

Can you please upload a sample Excel file with your data? Be sure to connect the query to data in the Excel file, not an external source.

Thanks,

Mynda

Sorry for not including sample data. I'm trying to add 2 columns to the query. I would to create 2 new columns (Previous Hours & Increase Since Last Invoice). So when the Total Hours columns updates, the previous total hours will be moved to the Previous Hours column and the increase in hours for each PO # will show in the Increase Since Last Invoice column. Hope this helps.

Cheers!

Hi Tykru,

Thanks for sharing your file. You don't need Power Query for this. You can do it with a PivotTable > Show Values As > Difference From.

See file attached.

Mynda

Thank you so much for the quick reply, Mynda. I hadn't known about this handy feature, however, is there a way to do it without the date field? I'd like to keep it so that there is only one line per PO#, with the aggregated hours and the increase after that; I could probably leave off previous hours column as that is a little redundant. 

Thanks!

Hi Tykru,

Your question isn't clear. How do you tell what the last invoice was? I assumed it's based on date, but it's not clear if this data is for one customer or multiple customers. The PO numbers are not consistent i.e. one has a letter in it, so I assumed this data relates to multiple customers.

That said, you can change the fields in the Row labels of the PivotTable yourself to get the desired result. You'll need to reapply the Show Values As calculation for the new fields.

Mynda

Each PO (purchase order) is tied to only one project, and the total hours represent how many hours the engineer billed for technical review work related to the project; in my query I grouped the POs and selected sum for the hours column.  So although date is a factor, I'd like to leave the date field out of the table and just list each projects (PO) and how many total hours have been billed against it, and after the latest invoice has been added to the data source, the column after that will show the how many hours were billed against the PO in the most recent invoice. I hope I explained that a bit better, and again, thank you for the prompt reply.

Cheers!

Ok, but did you have a go at rearranging the PivotTable yourself to get your desired result? If you got stuck, please upload your file again so I can see where you went wrong and point you in the right direction.

Mynda

I tried rearranging the pivot but I don't want the invoice date and # in the table, just PO# and the sum of total hours by PO#, and then the increase of hours per PO#; the pivot you made has difference from, which shows negative #s. I was hoping that Power Query could solve this by summing total hours grouped by PO (which I already created in my query) and then when there is a new invoice related to a PO#, show in a new column the increase in billed hours from the previous aggregated total. Hope this makes sense.

Thanks!  

Hi Tykru,

Can you please provide a mock-up of what you'd like to see in Excel. It doesn't have to include all of the data, just a few of the PO#s. It needs to reconcile to the data you have provided so I can trace it back.

Mynda

Thanks Mynda. I think this is a bit more complicated than I initially thought because I'd like to see what the increase since last invoice is but not all POs are billed each invoice, so there will be many POs that have 0 while the only increase will be for the handful of POs on the most recent invoice. Attached is a mock-up of 3 POs; PO 67778 was billed for the most recent invoice on 3/27/18, and the other 2 POs were not. Essentially, the end result I'd like to show in the query are the total hours invoiced to date, and the increase from last invoice. The increase is useful because we don't want to see the engineers exceed an 8 hour threshold per PO per invoice. I'm thinking that the Previous Invoice Hours column is redundant and difficult to calculate because the most recent invoice date varies depending on the PO, so I guess it makes sense to not base any of the calculations off of the date, as this not a constant variable. Perhaps it makes more sense to just show Total Hours invoices and the most recent increase, and if null, then to show 0.

Tykru

Hi Tykru,

Please see Excel file attached. You can do this with Power Query by extracting the various pieces of data and then merging the queries back together.

Mynda

Sorry I thought I already replied. This is perfect. Thanks Mynda!

Mynda Treacy said
Hi Tykru,

Please see Excel file attached. You can do this with Power Query by extracting the various pieces of data and then merging the queries back together.

Mynda  

Mynda,

I haven't had a chance to parse one of the queries in your attached solution until now, but can you please clarify the below code for the query named Last Invoice?

= Table.SelectRows(Source, let latest = List.Max(Source[Invoice Date]) in each [Invoice Date] = latest)

The name of the step is Filtered Rows; I know you can use the query editor to filter but it seems there's no filter on any of the columns.

Thanks!

Tyler

Hi Tyler,

I just used the filter buttons. You'll see there is a filter on the Last Invoice query Invoice Date column.

Mynda