none
Copy Template from one sheet to another based on condition RRS feed

  • Question

  • Hi All,

    I have 2 sheets :
    1) Details : Contains all the data.
    2) Dummy Template : Contains standard list of data which needs to copy from this sheet to Detail sheet

    Logic :- 1) Check minimum lowest date in Details sheet in Posting date( column I). and identify that lowest date weeks Monday date.
    Now we need to copy paste the template from Dummy Template sheets by repeating Point 1 Mondays date till Current weeks Mondays Date.

    For Example : Minimum Date in Details sheets is 03-DEc-18, now we calculate Monday on this week and which is same 03-Dec-18.

    Now we copy all the row from range A2:AJ17 and paste in Details sheets in bottom of last records. At Posting date will be start from 03-Dec-18. then repeat copy paste same rows A2:AJ17 till current week.

    Sample file is in below given link

    https://1drv.ms/x/s!Ap80Ku6M2Tw5gUOYV-OcgcvjfFU_

    Thanks in advance for your valuable support.

    Regards,
    Rajender

    Rajender

    Tuesday, January 15, 2019 4:59 PM

Answers

  • Hi Rajender. I don't think PQ can be used to copy a template from one sheet to another and then fill in the data.

    Ehren

    • Marked as answer by AskQuery1984 Thursday, January 17, 2019 6:19 PM
    Thursday, January 17, 2019 5:44 PM
    Owner
  • Hey!

    I've simplified the example to make it easier to understand. I lose focus when I see too many columns, so simplifying it makes it 10x easier to read and understand.

    Here's the file.

    There's a query that finds the earliest date and if it's not a Monday then it grabs the previous Monday and basically creates that data with the template table for those missing dates that you want to fill.

    Is this what you're looking for?

    • Marked as answer by AskQuery1984 Friday, February 1, 2019 9:15 PM
    Thursday, January 31, 2019 7:01 AM

All replies

  • Hi Rajender. This sounds like more of a VBA question than a Power Query one. Or am I missing something?

    Ehren

    Wednesday, January 16, 2019 7:50 PM
    Owner
  • Hi Ehran,

    Indeed it can be done via VBA, But I am learning Power Query now a days and looking if this type of problems can also be solve via Power Query or not ?So looking for the expert advice here in this forum.


    Rajender

    Thursday, January 17, 2019 7:25 AM
  • Hi Rajender. I don't think PQ can be used to copy a template from one sheet to another and then fill in the data.

    Ehren

    • Marked as answer by AskQuery1984 Thursday, January 17, 2019 6:19 PM
    Thursday, January 17, 2019 5:44 PM
    Owner
  • Hi Ehren,

    Thanks a lot for your valuable feedback.

    Regards,

    Rajender


    Rajender

    Thursday, January 17, 2019 6:19 PM
  • Hey!

    I've simplified the example to make it easier to understand. I lose focus when I see too many columns, so simplifying it makes it 10x easier to read and understand.

    Here's the file.

    There's a query that finds the earliest date and if it's not a Monday then it grabs the previous Monday and basically creates that data with the template table for those missing dates that you want to fill.

    Is this what you're looking for?

    • Marked as answer by AskQuery1984 Friday, February 1, 2019 9:15 PM
    Thursday, January 31, 2019 7:01 AM
  • Hi Miguel,

    Thanks a ton, Let me cross check the logic and test in original file. will get back to you ASAP.


    Rajender

    Thursday, January 31, 2019 7:09 AM
  • Hi Miguel,

    I was searching for same solution. and I partially check and successful.

    In case any further issue face will get back to you.

    One again thanks a ton for your help. Will try understand the logic and M-code you applied here.


    Rajender

    Friday, February 1, 2019 9:15 PM