Power Query to insert rows?

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,744
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
any representative example of source data and expected result from this source?
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,744
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • Capture.jpg
    Capture.jpg
    132.9 KB · Views: 4

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,744
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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?
Capture.png
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
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

Well-known Member
Joined
Feb 3, 2010
Messages
9,744
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
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

Well-known Member
Joined
Feb 3, 2010
Messages
9,744
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 3, 2010
Messages
9,744
Office Version
  1. 365
Platform
  1. Windows
Link to file with sample raw data and manually created output, hopefully the comments are clear to explain the required transformation: Example File
 

Watch MrExcel Video

Forum statistics

Threads
1,129,296
Messages
5,635,364
Members
416,856
Latest member
silentir

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top