Differences between Advanced Option and Advanced Editor and their language

[Ha_Da]

Hi All,

I am totally new for Power BI and investigate it for a month. Now I need to load data from Oracle database using advanced option. Which language should I use in Advanced Option field? I still found that there is a Advanced Editor when edit query. What is the differences between Advanced Option and advanced Editor?  What kind of formules should I use in advanced editor? Please share me links or files if you can. Thanks in advance

[ABC11]

Hi All,

I am new to power BI. When I connect to Oracle data source.Begining I was able see Advanced Option where I can write SQL query (like Select * from..... ). but now I am not able to see Advanced option diglog box. please, help me how can I see that box again where I can write my SQL query

Thanks,

ABC11

[Ha_Da]

Fundamentally the difference between advanced options and advanced query or query edit mode is where the execution of operations is being performed.

Advanced Options allows you to write custom SQL queries that are executed against the data source.

Advanced query allows you to write custom M query code against data that is already imported into the workbook.

Advanced options = Executed against original data source on data extract process

Advanced Query = Executed locally in workbook against imported data

[v-sihou-msft]

@Ha_Da

The Advanced Option is the underlying query your want to execute for retrieving dataset from your dataset. It can be T-SQL, MDX, DAX, etc, depending on your data source.

The Advanced Editor contains the applied steps onto dataset which are created in Power Query Language (refered to as M). For more detail information, please refer to links below:

Power Query (informally known as "M") Formula reference

Learning resources

Regards,

[Ha_Da]

Thanks for your help.

[BhaveshPatel · Accepted solution]

Hi @Ha_Da

Advanced option in the dialog box shown is pulling the subset of data from the Database ( For Example, Instead of full table, you need only first 100 rows etc.). SQL statement is used in that case.

SELECT *

FROM XYZ

Advanced Editor is recording all the ETL steps in the "M" Language. This is the only language supported in the Query Editor. I would suggest you to buy "M for data monkey" if you are really serious to learn the language. There are quite a few good blogs  as well which will guide you to learn M.

Google below blogs

1.Chris Webb's BI Blog

2. Ken Puls blog

3 Imke Fieldman Blog.

[Ha_Da]

Thank you very much. It do help me.