cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
sunilege Frequent Visitor
Frequent Visitor

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)

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: I need create a custom table based on sql query on top of data which there in dataset

@sunilege,

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

1. Group by the table as below.
1.PNG

2. Expand ALLdata column.
2.PNG

3. Create a custom column using code below.

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



3.PNG

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

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.

5.PNG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Super User
Super User

Re: I need create a custom table based on sql query on top of data which there in dataset(imported x

Come again?


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Moderator v-yuezhe-msft
Moderator

Re: I need create a custom table based on sql query on top of data which there in dataset(imported x

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
sunilege Frequent Visitor
Frequent Visitor

Re: I need create a custom table based on sql query on top of data which there in dataset

Capture.JPG

 

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 )

Oracle_sol.JPG

 

 

 

How can I acheive this in Power BI ?

 

Moderator v-yuezhe-msft
Moderator

Re: I need create a custom table based on sql query on top of data which there in dataset

@sunilege,

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

1. Group by the table as below.
1.PNG

2. Expand ALLdata column.
2.PNG

3. Create a custom column using code below.

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



3.PNG

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

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.

5.PNG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

sunilege Frequent Visitor
Frequent Visitor

Re: I need create a custom table based on sql query on top of data which there in dataset

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.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 59 members 1,104 guests
Please welcome our newest community members: