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]

sure, i'll try tomorrow

 

[JackDanIce]

Much appreciated, ty in advance

 

[sandy666]

Filter DV (Account) doesn't work
Performance is no calculated, manually entered? If you want manually enter any value to the Query Table it doesn't make sense because your changes will disappear after refresh
Calculation for Amount and Balance is very weird, mixed logic

it is not clear why in the same time are different values

Date	Account	Currency	Amount
28/09/2006	Apple	EUR	50000
28/09/2006	Apple	EUR	-50000

and

01/01/2010	Banana	USD	-52092.5
01/01/2010	Banana	USD	52092.5

note: Power Query works with tables and columns not with selected cells in different places, maybe revise your approach to the problem

 

[JackDanIce]

Hi, thanks for taking a look, let me try to clarify:

Nothing "works", it's just to mock up the output and input. The Output is to show what it should look like, not function like.
The drop down is manually inserted to to indicate how the user would select an account.

Don't worry about the differences in Apple or Banana, each row is a unique record, then it's just filtered and additional rows added - that is all.

I imagine Power Query steps to be:

Read source data into Power Pivot
Filter this data by Account name
Insert the month-end rows (that need to be calculated)
Print this to the sheet

This is to be my final output, I can fill in the rest via macro or manually that isn't being asked.

I just need Power Query to create the output layout/format as shown.

The cells in yellow are not to be solved with power pivot, ignore anything in yellow please, it's just to show the final output as you'd asked.

Nothing else please, thank you

 

[sandy666]

hahaha, ok
I'll try but one more thing: why there are duplicated dates with different values?

and how many rows you have in the source table?

 

[JackDanIce]

Think of your bank statement, you might buy more than 1 coffee in day and demand a refund because they used the wrong kind of fatty milk! Two transactions, 1 date.

Now imagine the raw data is a transactions list of a bank's entire list of customers AND it does not have calculated month-end balances. It's just a raw list of transactions.
Next, a customer asks for their bank statement and wants month-end balances.
The bank has to filter the data, only for this customer and their transaction records and then insert rows for month-end balances (and calc these balances).

I can do the calc parts, just need help to insert these month-end rows!

 

[sandy666]

and how many rows you have in the source table?

 

[JackDanIce]

The file I have contains 41k rows (separate, individual transactions). This is not the full set and just for prototype/develop and test purposes.

 

[sandy666]

41k rows

so manually insert rows is not a solution

 

[JackDanIce]

No, filter the 41k by account name, use Power Query to insert those rows, then print out?