I need create a custom table based on sql query on top of data which there in dataset(imported xl)

[sunilege]

I need to create a custom table based on ssome sql query on top of data which is already avaialable in dataset( say dataset is an excel data whicih is imported as a dataset into Power BI)

[v-yuezhe-msft]

@sunilege,

Please share sample data of your table and post expected result here following the guide in this thread:https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490.

Regards,
Lydia

[sunilege]

In DB like Oracle I can make this via a SQL query to select the data as required on a base table for visualization purpose.

Say dataset is EMP_ATTEMPTS

Sql code in Oracle : select * from EMP_ATTEMPTS
where emp_no || attempt_no in ( select emp_no || max(attempt_no) from EMP_ATTEMPTS group by emp_no )

How can I acheive this in Power BI ?

[v-yuezhe-msft · Accepted solution]

@sunilege,

In Query Editor of Power BI Desktop, duplicate your current query, then perform the following steps.

1. Group by the table as below.


2. Expand ALLdata column.


3. Create a custom column using code below.

=if [Max_attemp no]=[ALLdata.ATTEMP NO] then 1 else 0

4.Filter the custom column to show value 1, then you can remove the Max_attemp no column and custom column.


Alternatively, if your original table is still in Oracle table, you can enter the Oracle SQL statement into  the highlighted textbox and get expected dataset into Power BI Desktop.



Regards,
Lydia

[sunilege]

Thankyou v.much Lydia It worked.

So there is no sql techniquie as such to modify/tranform data based on which one can write a sql stmnt.

[Greg_Deckler]

Come again?