Forum

Ho to add a regular...
 
Notifications
Clear all

Ho to add a regular formula into power querry?

5 Posts
2 Users
0 Reactions
738 Views
(@thenewbee)
Posts: 26
Eminent Member
Topic starter
 

Hello,

 

is it possible, to add in power query an additional column, which works with an regular formula?

I would like to add a simple if else formular from values of one cell from next column on same leven and one cell below.

=C9<C8

 
Posted : 25/01/2021 10:50 am
(@mynda)
Posts: 4761
Member Admin
 

Welcome to our forum!

It's best not to add formulas to tables that are the output of queries as there's no guarantee they'll stay in alignment. Please upload a sample file showing what you want to do. We can then use that to show you how this is done with Power Query. 

Mynda

 
Posted : 25/01/2021 9:08 pm
(@thenewbee)
Posts: 26
Eminent Member
Topic starter
 

Hello Mynda , many thanks for your reply.

 

well, my challange based on this topic:

https://www.myonlinetraininghub.com/excel-forum/power-query/power-query-table-refreshing-and-adding-rows-in-same-table

 

Here I import data from an external source, and have a loop inside, which removes dublicate entries.

 

So, I got an table by power query, and try to add later on in the worksheet in column "G" the formula above. Theoretically it works, but now it´s somehow included into the "olddata" and in each refresh I get a new column.

I tried various ideas, without success, so I would like to include the formula somehow into the power query 🙂

trial2.PNG

 

Thank you upfront, I´m sorry, I´m not an native English writer.

 
Posted : 26/01/2021 5:02 pm
(@mynda)
Posts: 4761
Member Admin
 

Hi and thanks for sharing your file.

The reason you are getting duplicates is because you're referencing the output table in the query itself, but I think you figured that out already. This is the incorrect way to reference the previous row, as you've found. You should be performing the formula in column G inside of Power Query. Please see this tutorial on using Power Query to reference the previous row - Option 2. 

From there you can perform the formula in column G inside Power Query.

Let me know if you get stuck.

Mynda

 
Posted : 26/01/2021 7:58 pm
(@thenewbee)
Posts: 26
Eminent Member
Topic starter
 

Hello Mynda, many thanks for that.

 

I got it from your tutorial, it´s great in general (and the other tips, too). We expand the talbe and shift re results one above.

In Option 2, setap 6 , the third picture describes it perfect. Confused

 

Well, while I read this, I got also the idea, just to remove in my "olddata" the added column and this works too. (very simple according my previous triesConfused)

I belive, the better solution would be still, to have it inside of power query, but I think as long a I try various formulas, to get my result, I keep it outside as I´m more familar with standard formula.

 

Thanks a lot, also the whole tutorial archive was unknown yet 🙂

 
Posted : 27/01/2021 11:23 am
Share: