cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Create new row referring existing column in table

Hi, 

 

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

 

 Capture.JPG

 


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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Anonymous
Not applicable

Re: Create new row referring existing column in table

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:

 

Input.JPG

 

 

 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:

 

Output.JPG

 

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

 

Thank you for your patience.

 

Regards,

Arjun

 

View solution in original post

6 REPLIES 6
Microsoft Phil_Seamark
Microsoft

Re: Create new row referring existing column in table

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.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Anonymous
Not applicable

Re: Create new row referring existing column in table

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

Microsoft Phil_Seamark
Microsoft

Re: Create new row referring existing column in table

Hi @Anonymous

 

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Anonymous
Not applicable

Re: Create new row referring existing column in table

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.

 

Output1.JPG

Regards,

Arjun

Microsoft Phil_Seamark
Microsoft

Re: Create new row referring existing column in table

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.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Highlighted
Anonymous
Not applicable

Re: Create new row referring existing column in table

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:

 

Input.JPG

 

 

 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:

 

Output.JPG

 

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

 

Thank you for your patience.

 

Regards,

Arjun

 

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors