Forum Discussion
I need create a custom table based on sql query on top of data which there in dataset(imported xl)
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)
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
5 Replies
- Greg_Deckler
Community Champion
Come again?
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,
LydiaIn 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 ?
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
