Results 1 to 4 of 4

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

  1. #1
    Conjurer Ed Kelly's Avatar
    Join Date
    Jul 2016
    Posts
    164
    Articles
    0
    Excel Version
    2016

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Last edited by Ed Kelly; 2016-07-20 at 02:11 PM. Reason: meant to include 2nd sheet in workbook no hyperlinks

  2. #2
    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

    Code:
    =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.

  3. #3
    Conjurer Ed Kelly's Avatar
    Join Date
    Jul 2016
    Posts
    164
    Articles
    0
    Excel Version
    2016

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

    Thank You

    Quote Originally Posted by SteelReyn View Post
    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

    Code:
    =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.

  4. #4
    You're welcome!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •