Forum Discussion
Modifying table in Excel connected to Power Platform
Hi gogiwine,
Thanks for sharing the screenshot, that makes it clear.
In Excel, when we connect to a Power BI dataset it works as a live connection. Because of that, the Edit Query option doesn’t open Power Query like it normally does, so you are not able to click it this is expected.
If you need to bring in new columns, there are only two ways:
-
Go to the connection properties and update the Command Text the DAX query you see there to include the new column.
-
Or, the simpler way is to go to Get Data > From Power Bi Data set again and insert the table freshly with the extra fields selected.
So basically, in this type of connection you cannot open Power Query, you will have to either change the command text or re-insert the table.
Hope this clears it up.
Regards,
Community Support Team.-
If this is a live query into the dataset, you will need to go back to Excel and edit the query in Excel.
Launch Power Query Editor.
In the Power Query editor, modify the query to include the newly added columns from the Power BI dataset, apply & close, then your Excel table will refresh with the additional columns.
Thank you for the answer, but this part "modify the query to include the newly added columns from the Power BI dataset" is where I'm stuck. There is no place for me to include new columns or make any modifications to the inserted table
Hi gogiwine,
Thank you for posting your query in Microsoft Fabric Community Forum. Also, thanks to MasonMA, for those inputs on this thread.When you bring data from Power BI dataset into Excel, it is a live connection. You can only pull fields which are already published in that dataset. If a new column is added later in Power BI, first make sure the dataset is refreshed and published.
Then in Excel, open Data > Queries & Connections > Edit → this will take you to Power Query. There you can expand the fields again and select the new column. After applying and refreshing, the extra column will come into your table.
If you still don’t see it, that means the new column is not yet available in the Power BI model and you may need to check with the dataset owner.
Hope this helps if you have any queries we are happy to assist you further.
Regards,
Community Support Team.Thank you. However, I can't click "Edit Query". (see below)
Did I do something wrong when inserting the table? Or do I need to update "command text"?
(not sure about security, so just hide the random characters in connection string)
Hi gogiwine,
Thanks for sharing the screenshot, that makes it clear.
In Excel, when we connect to a Power BI dataset it works as a live connection. Because of that, the Edit Query option doesn’t open Power Query like it normally does, so you are not able to click it this is expected.
If you need to bring in new columns, there are only two ways:
-
Go to the connection properties and update the Command Text the DAX query you see there to include the new column.
-
Or, the simpler way is to go to Get Data > From Power Bi Data set again and insert the table freshly with the extra fields selected.
So basically, in this type of connection you cannot open Power Query, you will have to either change the command text or re-insert the table.
Hope this clears it up.
Regards,
Community Support Team.-