Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHello @handwepg
Click on "Edit Queries" and then on "Advanced Editor"
You will see a text restricting maximum number of columns as highlighted below:
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
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsI 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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi,
What exact formula are you writing in Power Query?
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |