Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
handwepg
Helper I
Helper I

Why won't new column added to table show up on PowerQuery?

I've added a new column in one of my tables using the New Column button from the Modeling tab in Power BI Desktop.  However, when I go into Edit Queries from the Home tab, that new column does not appear among that table's columns in PowerQuery.

 

What am I missing?

 

thanks

1 ACCEPTED SOLUTION

Hi @handwepg,

 

In the query editor you need to add a column similar to this:

 

(if [A] = null then 0 else [A]) + (if [B] = null then 0 else [B]) 

This will give 0 when the A or B column is null and sum result of both columns when you have values on both.

 

No need to change null to 0.

 

Regards.

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

8 REPLIES 8
Waseem
Helper II
Helper II

Hello @handwepg

 

Click on "Edit Queries" and then on "Advanced Editor"

You will see a text restricting maximum number of columns as highlighted below:

Capture 2.JPG

 

 

In my case it is showing 158 because i have 158 columns in my table. In your case, whtever number you see here, just type one more than that (if its 9, change it to 10). Save query and you are done. your new column will be added.

 

Cheers

 

Anonymous
Not applicable

Hello Waseem,

 

Though you gave a useful information but for my .xlsx file I couldn't see any restriction with the number of columns being exported, it might be applicable for CSV alone or some change might have happened from 2018. But thanks for your information.

Hi @Anonymous ,

 

Are you missing some columns when importing a .xls file? Did you just updated the source file and the columns aren't showing?

 

Be aware that if you have the connection to an excel spreadsheet, when you start making your changes the columns you work with are saved on the query steps and you may need to add that column manually to the first step where they get the detail.

 

This depends off course on the way the setup is made, and how you are making the transformations.

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Thanks for your tips @MFelix 

MFelix
Super User
Super User

Hi @handwepg,

 

Power BI uses two different languages to make the calculations, on the "front end" it uses DAX a language similar to EXCEL, when you are on the query editor it uses M language.

 

Although some thing that you do on the "front end" of PBI it as a effect on the query editor, like renaming columns or deleting tables or columns, other don't are replicated in both PBI enviroments, this is the case of the creation of new columns, the fact that you are using DAX since it's a different language with different arguments it is not reflected on the query editor that's why you are no seeing the columns on the query editor.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



I see. Thanks.

 

Follow up question. When I created the new column in the "front end" I was adding together two columns (e.g. A and B) to make one new column (e.g. C).  It was true in every row that either A or B had a numeric value.  If A had a value, B was null.  If B had a value, A was null.  The resulting new column C then always had a value.

 

However, when I tried "Add Column" in Power Query (i.e. DAX) and I used the same formula from the "front end," every value in the new column C comes back as null.

 

Why is that?  And, more importantly, what should I do about it?  Do I need to go back into the source table columns A and B and convert all null values to zero?

 

Thanks again

Phil

Hi @handwepg,

 

In the query editor you need to add a column similar to this:

 

(if [A] = null then 0 else [A]) + (if [B] = null then 0 else [B]) 

This will give 0 when the A or B column is null and sum result of both columns when you have values on both.

 

No need to change null to 0.

 

Regards.

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi,

 

What exact formula are you writing in Power Query?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.