Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
5 years ago

Filtering tables during import

Hi experts, I would like to import a table whose first column contains an order number, but for which there are several rows, since there are different processes for each order. now I don't want to import all processes, but only the process with the highest process number per order. how can I filter this before importing the data into powerbi. the table actually has 250000 lines.

Thanks for the help,
Tober

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Syndicate_Admin , if you want this logic in the power query, you have to bring all data and filter.

     

    Better have it SQL and use SQL while creating the connection to bring data 

     

     

     

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    5 years ago

    Hi Syndicate_Admin ,

     

    What is your data source?

     

    If it is SQL Server, you can try the following code in advanced options:

     

    SELECT T2.[Order], T2.[list], T1.[Process]
FROM [AdventureWorksDW2017].[dbo].[Table_Process_Test] T1
INNER JOIN (
	SELECT [Order], max([Process Number]) list
	FROM [AdventureWorksDW2017].[dbo].[Table_Process_Test]
	GROUP BY [Order] ) T2
ON T1.[Order] = T2.[Order]
	AND T1.[Process Number] = T2.[list];

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

    Best Regards,
    Winniz

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    5 years ago

    Hi Syndicate_Admin ,

     

    Does your problem have been solved? If it is solved, please mark a reply which is helpful to you.

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

     

    Best Regards,
    Winniz