locked
Solution to Add One Month to Latest Date in a Column RRS feed

  • Question

  • Hi,

    I'm currently trying to create a data warehouse in Power Query for use in Power Pivot based on historic accounts data. The excel workbooks from our accounts system are stored by business entity, and contain the last six month's accounts data in pivoted format. I have built a query which combines all the files in my folder, and cleanses and unpivots the data, which seems to work well.

    The issue I have is that the five prior months all have correct month names as column headers, but the latest month has 'ACTUAL' as text rather than the month name. So when I Unpivot the data, I do not have the correct month name for the latest month.

    Is there a way to replace the ACTUAL text by adding one month to the Max value in the date column? I have tried various things but am struggling to find a correct solution.

    This is where I've got to: -

    INDEX Description CODE LineNo Month Value

    0 Account1 Code1 1 01/09/2015 100.00

    1 Account2 Code2 1 01/10/2015 250.00

    2 Account3 Code3 1 ACTUAL 300.00

    Thanks for any help.

    Monday, May 16, 2016 5:14 PM

Answers

  • The following works on the sample data you provided. Let me know if this helps.

    Just click the little fx button next to the formula bar, and a new step will be added. Then paste the following into the formula bar, replacing "PreviousStep" with the actual name of the previous step.

    = Table.ReplaceValue(PreviousStep, "ACTUAL", each Date.StartOfMonth(Date.AddMonths(PreviousStep{[INDEX] - 1}[Month], 1)), Replacer.ReplaceValue, {"Month"})

    Ehren

    Monday, May 16, 2016 10:46 PM

All replies

  • The following works on the sample data you provided. Let me know if this helps.

    Just click the little fx button next to the formula bar, and a new step will be added. Then paste the following into the formula bar, replacing "PreviousStep" with the actual name of the previous step.

    = Table.ReplaceValue(PreviousStep, "ACTUAL", each Date.StartOfMonth(Date.AddMonths(PreviousStep{[INDEX] - 1}[Month], 1)), Replacer.ReplaceValue, {"Month"})

    Ehren

    Monday, May 16, 2016 10:46 PM
  • Hi Ehren,

    Thanks - I actually didn't word my question correctly, as I had already converted the text in the Month column to DATE format, and it had replaced the ACTUALS with Error!! Therefore there was nothing called ACTUAL in my data :s

    My solution was to go back a few steps before I converted to DATE format, and replace ACTUAL with a dummy date (01/01/2000). I then substituted ACTUAL for the dummy date in your code: -

    = Table.ReplaceValue(#"Renamed Columns1", #date(2000, 1, 1), each Date.StartOfMonth(Date.AddMonths(#"Renamed Columns1"{[Index] - 1}[Month], 1)), Replacer.ReplaceValue, {"Month"})

    This now works perfectly and will work for all the files I import regardless of what year/month!

    Thank you.


    Tuesday, May 17, 2016 9:08 AM
  • Great!

    Ehren

    Tuesday, May 17, 2016 4:58 PM