Power Query to insert rows?

[JackDanIce]

Hi,

This relates to post: Flat table -> Pivot table -> Insert calculated row?

I have a named table of data, duplicated dates in column A (ranging over 15 years), a label in column B and various metrics in remaining columns.

For output, I need to extract a specific label and then insert an empty row with just the month-end date for every month across the range of dates in this subset. Some dates will already be at month-end but I still need to insert this row and ensure column B includes that filtered label value

I think this is like a UNION join instead of an INNER join because I want to insert rows (with month-end dates) into my subset table, where dates between the two tables may not always match? My SQL isn't strong.

Is this more suitable for Power Query, read the data from the sheet and then create this transformation, before output?

If so, can anyone explain or guide through steps how to do so?

TIA,
Jack

 

[sandy666]

any representative example of source data and expected result from this source?

 

[JackDanIce]

The white rows are from the source data e.g. filtered for account Sandelman Partners Multi Strategy (final output will differ slightly but this suffices for now, ignore split for Local and Base CCY)
The yellow rows are what I need to add, the rows in white direct from or calculated in the source data

The min date month for this account is Sep 2006, Max 31 May 2007, so need to insert rows for every month end between for Date e.g. 30/Sep/06, 31/Oct/06, 30/Nov/06 ... 31/Mar/07
Currency won't vary per account/data subset
Amount (yellow)= sum of all amounts for that month only {i.e SUMIF month and year of Date column matches the calculated month-end date} (A)
Performance (yellow rows only) = 1+ lookup value (B) (both performance columns are identical, only a single column will be in output)
Balance = (A) * (B)

The steps I image are:
Filter source data by Account
Define min and max date
Create a table of month-end dates between min and max date inclusive.
Union(?) These two tables together and sort by Date ascending
Apply calculations as above

 

[sandy666]

Hm, I think for the picture the best is photoshop

 

[JackDanIce]

Apologies the last 3 rows should be highlighted yellow also.

I forgot to state, if there are no transactions in a month, then the month end amount is the same as the previous month-end closing balance (Jx = Kx-1)

Hm, I think for the picture the best is photoshop

Any better?

 

[sandy666]

as I said: use Photoshop for the picture
or
use XL2BB to post source data and expected result in the post
or
post a link to the shared excel file with representative example of source data, expected result and any code if any - using onedrive, googledrive, dropbox or any similar

 

[JackDanIce]

I'm afraid I do not have Photoshop to use. I have used a screenshot as this seems permissible.

On a separate laptop and PC, using Google Chrome and checking on MS Edge I'm able to see the image clearly if I click on it and where it shows input (source data) and expected output, with desired change highlighted in yellow.

In the image A4:E9 is source data, expected result/output is in G4:K17. At bare minimum, I only need to insert the rows in yellow with their respective date values.

If still not clear, I'll mock up a file later and attached.

 

[sandy666]

about Photoshop (graphic software) it was irony
nobody want to retype data from the picture
so the best way is last option from the post#6 (with source data, power query, pivot table, expected result)

 

[JackDanIce]

lol thanks and understand no-one wants to type data from the picture. Need to step away from PC, will post a dummy file soon.

 

[JackDanIce]

Link to file with sample raw data and manually created output, hopefully the comments are clear to explain the required transformation: Example File