none
Waqas RRS feed

  • Question

  • I have 3 years of data in a folder. Files contain data based on Financials years with months in headers for example FY 2014 starts with Jul-13 and ends on Jun-14, FY 2015 starts with Jul-14 and ends on Jun-15. I am creating Power query from folder and when Promote headers this only bring months from first file and ignore the fact that other files have different months. Please advise how to get over this.
    Monday, January 18, 2016 11:38 AM

Answers

  • You'll need to convert each file to a table with its own headers, and then append them together (rather than appending them up front, which loses the fact that they don't share the same column names). Writing a bit of custom M is probably the fastest way to do this (rather than manually converting each file one by one). If you can paste your current M formulas here (by opening the Advanced Editor and copying the contents), we can help you get something working.

    Ehren

    Tuesday, January 19, 2016 5:47 PM
    Owner

All replies

  • When you use a folder as your datasource, Power Query operates on the assumption that the data has the same headers. Its assumption is that each file is a chunk of a single table, sharing headers, with each just being a subset of all the rows in the table.

    The action it applies is 'Append', which stacks two queries on top of each other. What you've described is a pivoted data source. Each will have to be unpivoted before appending together.

    Check out the <a href="http://PowerBIPhilly.org">Power BI User Group of Philadelphia.</a>
    Our next meeting is February 2 in Malvern, PA.

    Monday, January 18, 2016 2:06 PM
  • You'll need to convert each file to a table with its own headers, and then append them together (rather than appending them up front, which loses the fact that they don't share the same column names). Writing a bit of custom M is probably the fastest way to do this (rather than manually converting each file one by one). If you can paste your current M formulas here (by opening the Advanced Editor and copying the contents), we can help you get something working.

    Ehren

    Tuesday, January 19, 2016 5:47 PM
    Owner
  • I have a similar project. Some files have long names, others short, all with 4 columns the same and then up to five columns with a set of different names. Key data was embedded into the filename, Type, Date, Unit. I put all the long files named items in one folder, short into another. Then used the folder function to parse out all the items into an unpivoted set, Unit, Date, Type, Column Name and Amount. Then just appended them all together. Is your data from year to year similar? It sounds like a file of monthly accounting items. The columns you want are all probably the same, acctno, date, amount sort of stuff. If the columns are not the same in each year's files, you could rename them by position to a common set of name to end up with. If there are dates, like date posted, do you need the period headers at all and can substitute the date info by formula?
    Wednesday, February 17, 2016 2:05 PM