Added Custom Columns in Query Editor Not Working in Data Table

[mz1290]

I am working a project and attempting to use BI as my visualization tool.  My data sets are very basic and require a few custom calculation fields.  I created all my required fields in the query editor.  Once I saved my work and applied it to the data table I noticed that columns are appearing but none of the calculations are working.

Is anyone else encountering this issue?  It works fine on query editor page but does not transition to the data table.

[BhaveshPatel]

Have you applied the Query Changes before loading to the PowerBI Desktop. Have not encountered such an issue before.

[mz1290]

Bhavesh,

Yes, after I finished creating my calculated columns I selcted the "close & apply" button.  The columns are appearing in the data set but the individual cells are not being populated (they are populated in the query view). I was mostly wondering if anyone else was having this problem after the recent update.

[MarcelBeug]

Possibly this is related to an issue I reported back in September 2016: when you provide a data type as the 4th parameter of the function Table.AddColumn, any data in the column that has another type, is still displayed in Query Editor, but it won't load into your tables.

[mz1290]

Marcel,

That does sound very similar.  Did you happen to find a workaround?  Have you just restructured your models?

[MarcelBeug]

Hi mz1290,

Actually I happen to be in a luxury position of just exploring Power Query without having (to bother about) any models.

My suggested workaround is not to use the 4th parameter of Table.AddColumn, but instead adjust the data type after the column is created, in a next step. That will generate a Table.TransformColumnTypes function, which will return errors for any data that can't be converted to the data type you supplied.

[mz1290]

Thanks for the info. I've done a lot of reading since I posted and have invested in a book about PowerPivot/PowerBI.  Hopefully, I'll pick up some best practices and when I'm ready to use BI I can go about it the most efficient way.

[v-haibl-msft]

@mz1290

I also don’t have encountered this issue. Could you please copy the power query in Advanced Editor and paste here? Maybe we can try the same scenario as you to see if it is reproducible.

Best Regards,

Herbert