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]

it will be a little complicated especially with dates

 

[sandy666]

so far...

Custom	Currency	Amount
28/09/2006	EUR	50000
28/09/2006	EUR	-50000
30/09/2006	EUR
01/10/2006	EUR	50000
31/10/2006	EUR
01/01/2007	EUR	-52092.5
01/01/2007	EUR	52092.5
31/01/2007	EUR

but I have no idea for

31/10/2006
30/11/2006
31/12/2006

 

[JackDanIce]

Sorry for late reply, I'm almost there, though my output is really slow to generate! I'm going to make a few minor changes (seems to be slower reading in data from the sheet and using merge queries to proxy a VLOOKUP than say adding column to table and letting Excel do the calculation).

I'll try to post the example workbook later, but fairly pleased it's almost done!

 

[sandy666]

seems to be slower reading in data from the sheet

may brain slowed down because of temperature here

 

[JackDanIce]

Hi Sandy,

I got Power Query to work and generate output as needed. It involved isolating min and max dates, then creating a list of end of month dates based on this, merged into main query results, then some more manipulation.

Problem became took on averge a 1minute for each data transform/output and the more I tried to reduce queries, handle calcs etc in Excel (I don't know why but the raw data will always be kept in the file and manually updated), the less "work" Power Query was doing but still taking ~1minute.

Finally, using additional helper columns and stuff inserted to reduce Power Query, so tried a VBA approach which now works and each output takes <3 seconds - much faster than avg 1min!

Thanks for reading and efforts to try. If I get time later, I'll try to post the version with completed Power Query working where pulls took avg 1 min, maybe you can suggest speed gains?

Best,
Jack

 

[sandy666]

Hi Jack,
Maybe try to add somewhere Table.Buffer or/and List.Buffer

 

[JackDanIce]

Thank you, I'll look those up and try applying