Are the new features for query editing available in Power Query in Excel as well?

[qshngv]

I'm using Excel 2013 and have Power Query as its own seperate tab now. 

Recently I started exploring functionalities of PowerBI and noticed some additional features such as convert column to list and invoke function in the Edit Queries panel than in Power Query in Excel.

Just wondering are those features also gonna be available through Excel? 

[BhaveshPatel · Accepted solution]

PowerQuery in Excel 2013 is available as add-in and These tools in Excel is considered as Version 1. Microsoft has fully focussed their BI efforts ion PowerBI as it serves as a complete ecosystem for Self-service BI. There is one exciting features called "Query Dependencies" added in recent release which would allow you to see any relations between two queries. Something like "alteryx". 

Creating Parameters is also newly added feature in PowerBI and It would be thrilling for you to know that you can use list of values, another query or single values as Parameters. How Exciting! and All of these is completely free with 1GB of cloud space to upload your datamodels. 

In Cloud, You can also use Analyze in Excel feature which will allow you to analyze your powerbi datamodel in Excel. Also, You can upload your powerpivot model in PowerBI Desktop.

So many things to do in PowerBI service and Desktop as well.

PowerBI is evolved from the PowerPivot in Excel( Personal BI) to the journey of corporate BI. Row level Security is one of the sexy feature of the PowerBI and would serve the purpose of the IT and Business Users as well.

[qshngv]

Thank you Bhavesh. I see, sounds like Excel PowerQuery and PowerBI Query Editor are going on their seperate ways now. Currently, most of our data models are build through Excel PowerQuery and loaded to PowerPivot to be consumed in PivotTables. We also have parameters tables within Excel workbook referenced by the query. Not sure how easy it is to migrate to PowerBI in this case. Just wondering does PowerBI have any reporting features similar to Pivot Table in Excel? Thanks again!

[samdthompson]

@qshngv Excel and powerbi going separate ways is an incorrect interpretation. They are becoming much more closely aligned. Power query in 2016 is not a separate addin but a fully integrated part of excel. The functionality between powerbi and query editor in excel 2016 is nil. The difference in powerbi query editor you have and the excel addin is that the addin has been superceeded by powerbi. That is not true of the excel 2016 query editor.

If you have parametised values, you will not be able to do that in powerbi unless you pass the data into a powerpivot model as you have now and then use that model as a data source for powerbi. If there are a limited number of values that could be used as parameters, you maybe able to build them into a a model and use a slicer type function to change your report. 

// If this is a solution please mark as such

[KHorseman]

Yeah, Power BI is going separate ways from Power Query in Excel...2013. Because 2013 is old now. 2016 is a different matter.

[samdthompson]

All part of excel 2016. There are a couple of DAX funtions too which are different in the DAX 2015 (excel 2016) and powerBI. If you need these new fuctions, use powerBI to do your analaysis and data manipulation then pass back to excel with the addin in as a data connection. If you can upgrade excel 2016 do that as it is, in my opinion a real improvement on 2013.

// If this is a solution please mark as such