power query YTD data create new table with month data

[Lars Ole Sørensen]

Is there a method to convert the YTD table data to a monthly table data with the same 5 columns with power query or some other method. The difference between the YTD data table and the Month data table should be the values in the column "Amount YTD", that the values in the month table should be monthly values. Hope someone can help me with this.

 

[p45cal]

A guess in the attached.

 

[Lars Ole Sørensen]

Sorry, I have the YTD data in the table, so I dont need them in the pivot table. I was maybe a little unclear in the text. I want to create a new table from the table that contains the YTD data to a table that shows Month data. I have updated the workbook with a excerpt og a table that shows the Month data I want for the whole table (Range I15:M19). I hope I am clearer to what I want to achive.

Kindly Regards
Lars Ole

 

[p45cal]

Understood.
Perhaps a quick half-solution in the attached using only a pivot table.
At cell I23, look at the Value field settings for the rightmost column; it uses difference from the previous date. This means there should be only exactly one date per month (seems to be so (the end of the month)).
A similar pivot at cell N23 which uses difference from the previous month. I'm not sure that the pivot differentiates between same months of different years.

I've filtered for the same data as your example, but you should clear those filters.
I realise it doesn't show the first value because there is no previous date (month).

 

[p45cal]

At cell O3 of the attached an attempt at a PQ solution. This one ensures there's only one date per month by converting all dates to the end of month date, regardless whether they were already so or not. It doesn't check whether months are contiguous, so if a month's data were missing you'd get a value which is the difference between 2 months.
Better?