adding a column to PQ table

K

konijay

New member
Joined
Apr 1, 2016
Messages
6
Reaction score
0
Points
0
Let's say that I've added a column(a calculated column with formula "=1") to the PQ table(output). I am able to do so and the formula copies all the way down since it's a TABLE.

However, if I refresh the data with more rows, the formula does not copy down all the way.

Does anyone know why or how I can make this happen? I'd rather put in calculated columns in excel table than in PQ since I am a lot better in excel formulas...

If I can do this, PQ would be PERFECT.

thanks y'all.
 
the best way would be to create that column in PQ or create a VBA process that will create that new column once the query is being refreshed. I recommend also sending a frown to the Power QUery team so they can work on this
 
I agree with Miguel. This is a known issue that formulas written in the table after load do not extend down. The only way to get this fixed is by giving them the feedback they need to hear, and you can do that be using the Send a Frown feature.

Since you're just adding a simple formula of =1, I'd suggest you do this in Power Query. Edit your query, go to Add Column --> Add Custom Column. Fill in the =1 there and give it a name. This way it will be generated in Power Query and will always extend down the entire table.
 
thanks guys..."=1" was just an example...I am OK in creating columns in PQ but I'd rather do some fancy formulas in tables.

I guess i'll send a frown..too bad PQ turns off the native excel table function.
 
it doesn't really turn it off, its just that the table is being recalculated every time you refresh it so it becomes a completely new table every time you refresh it. The Excel team needs to take in consideration the needs of excel users as ourselves tho so this can work the way that we want, but there could be some chances where you even get less rows than the last time that you refreshed your query.
 
Actually, that's not quite true... The table stays, and the data just gets refreshed. It's the same table, not a new one.

What's actually happening is that, when a formula is entered in a column, it's written into the underlying XML of the table. The problem is that there is a bug in the way tables extend (it's actually and Excel issue, not a Power Query one), and that is being hit with Power Query. You can actually trigger this same behaviour just using formulas in Excel and tables alone. If you're interested in this, I'm sure I can convince Zack Barresse to drop by and explain it in more detail.

Long and short... don't use formulas in a table that is based on Power Query.
 
You must log in or register to reply here.
Back
Top