Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
Solved! 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
@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_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
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.
Come again?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.