Help with: Power Query to handle multiple dates with individual values attached

Copper Contributor

I need to handle the data in the attached spreadsheet.

 

The currency values have multiple dates attached to individual values. However, the dates are repeated and I want to be able to sum all the currency values for a specific month.

3 Replies

@SudzP Hello

 

Is this what you are trying to achieve, check the Table1_2 tab and let me know. Thank you

@aghaffar82 

 

Thank you so much! that is exactly what I'm after.

 

If possible, please provide a brief explanation of what you did in the power query editor.

 

I can see more or less in the formulas, but a description would be helpful. I'm also not very familiar with the power query syntax

Hello, @SudzP 

 

Thank you for the feedback,

 

PowerQuery is a way powerful tool for such a messy and re-occurring tasks, in your case, I simply turned the whole dataset to an official Excel Table and from the Data Tab chose from the table (in the Get & Transform section) and loaded it to PQ. Once it loaded into PQ, I selected all the columns and chose unpivot columns which put everything in tow columns (column headings in one and values in the adjacent column) Then inserted conditional columns and then filled up, then added another set of two columns and again filled up and re-ordered the column and applied a filter to get rid of the nulls (empty cells/rows).

 

It's a bit of working initially but once you are done you are free to add as many columns in your source data table and with a single refresh, PQ will run all those steps and in a blink of any eye, you will have all the newly added/deleted updated in the report. The table at the right you see is a pivot table which I created after loading the data from the PQ.

 

Let me know if you still have some questions and I will be more than happy to answer:

 

You may follow me on YouTube and learn a lot more about such things and ask anything you wish to learn.

https://www.youtube.com/channel/UC6i9PkldOzNr1hxxUTEbg5w

 

Thank you