Create new row referring existing column in table

[Anonymous]

Hi, 

I'm just new to this forum and striving to achieve as below in Power Query only which fit to my requirement:

 

If the value in the Column-4 is greater then 0 then,

    1. new row should copy paste referring above record

    2. copied row should not copy the data containing in column 3

Appreciate your kind help.

Regards,

Arjun

[Phil_Seamark]

Hi @Anonymous

If you create a copy of your table, tidy up the columns then append it back on you should get what you need.

I have attached a PBIX file that has the workings for you.

[Anonymous]

Thank you for your quick response @Phil_Seamark. However, I'm trying to acheive same in Power Query not in PBI. 

Would appreciate if you can help on Power Query as well.

Regards,

Arjun

[Phil_Seamark]

Hi @Anonymous

The file I attached to the post was using Power Query for the transformations.  Did you download the file?

[Anonymous]

Hi @Phil_Seamark, 

Yes, I've downloaded the file which was in PBI format and tried the same as No of rows in my sheet are 1500. The output is appearing :

1. Column1 - Yellow color highlited are not been filled as I've used even Fill down option, but didn't worked out.

2. Even for if column4 value is less then 0 it is copied, which was not supposed to be.

Regards,

Arjun

[Phil_Seamark]

HI @Anonymous

You shouldn't need to use the FILL function.

Any chance you can share your PBIX file?  You can send me a link to it via private message if you prefer.

[Anonymous · Accepted solution]

Hi @Phil_Seamark,

My apology as I can't share the information. However, will try to present my requirement more precise:

I've 8 sheets in 1 workbook and have combined 8 in 1 using power query "Amend Queries" as depicted below:

 Here's output I need in my sheet:

Criteria: 

1. If an emp has leaves (Column-Leaves) it should include that hours in "Unbilled Hrs" column for that respective emp only or

2. If an emp has leaves (Column-Leaves) but don't have any "Unbilled Hrs" then new row should be created for that respective emp only and that leave hours should be shown in Total Hours with status as "Unbilled" in  Status Column (Yellow Color highlighted for your refer).

Example:  for Emp 4 - there is no Unbilled hrs but has leaves then, new row need to be created to respective emp for leaves and status should be Unbilled.

Output:

Hope this sufice you, please let me know how do i achieve this.

Thank you for your patience.

Regards,

Arjun