How to add the file name (month name) to the contents file for monthly credit card..

[Ed Kelly]

How to add the file name (month name) to the contents file for monthly credit card statement set up in power query as a CSV folder system (See attached Excel for images of the same)

Want to set up a pivot table dashboard for credit card statements monthly. (Fig 1)

Have dropped these XL CSV files into a separate folder with the pivot table dashboard file saved outside the same folder so that as I add new months data everything updates, simple so far!


The statement date is the seventh of each month and so I need to be able to in someway open the first column in power Query content column and keep the name in col 2 (fig 2)


Do not want to add a column to the raw data for each month, an extra step for non-Excel uses that might be confusing.


Can anyone tell me how to open the content column (Col 1) and add in the name in column two (such that I can split the name column month and just save the month and fill down if necessary).


This way my monthly payments to the credit card company will tie to the monthly spend each month given that the cycle ends on the 7th of each month not at the end of each month.

Have not uploaded the file given the sensitive nature of some of its content, hope I have supplied enough data.


Look forward to any input on this I'm pretty sure it's a simple fix just can't see it at the moment.

Thanks
Ed

 

[SteelReyn]

I think all you need is the Csv.Document formula. In your figure 2, don't open the Binary Content. Instead, add a custom column as

=Csv.Document([Content])

That will create a table column. From there, remove all columns except the Name column and the new column you just created. When you expand the table column, that will bring in all the contents of each csv file, and the first column will still be the name of the originating file.

 

[Ed Kelly]

Nice Job Steel, worked like a charm. Saved me hours trying to figure that one out.

Thank You

I think all you need is the Csv.Document formula. In your figure 2, don't open the Binary Content. Instead, add a custom column as

=Csv.Document([Content])

That will create a table column. From there, remove all columns except the Name column and the new column you just created. When you expand the table column, that will bring in all the contents of each csv file, and the first column will still be the name of the originating file.

 

[SteelReyn]

You're welcome!