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)
Go to 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
Proud to be a Datanaut!
@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
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_ATTEMPTSwhere 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 ?
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.
Engage and empower students with Power BI!
Continue your learning in our online communities.
Travel to Melbourne and network with thousands of peers!
Check out what's new in the Power BI Community!