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

Table filtering while import

Hello 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 every process, but only the process with the highest process number per order. how can i filter this before i import the data into powerbi. the table actually has 250000 lines.

Thanks for help,
Tober
2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Table filtering while import

Hi @Tober1996 ,

 

Please try grouping like this:

test_group_by2.PNG

Add a step in the advanced editor after expanding the table:

Table.SelectRows(#"Expanded expand",each([Max]=[expand.flow_id]))

test_result.PNG

Sample .pbix

Best Regards,
Liang
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

Highlighted
Frequent Visitor

Re: Table filtering while import

Hi together, thank you very much! The advanced grouping ist the solution, but it is not needed to write the extra code to see the other columns. You only have to press the button with the two arrows in the headline of the new column. than all existings columns expand and are shown again. 😄 

View solution in original post

7 REPLIES 7
Highlighted
Community Champion
Community Champion

Re: Table filtering while import

You can use Group By in the query editor to group on ID and get the max Process Order.

 

https://support.office.com/en-us/article/group-rows-in-a-table-power-query-e1b9e916-6fcc-40bf-a6e8-e...

 

Alternatively, you could creat a subgroup index on process order for each ID, and then filter to "1" on the new index column.  See this article on creating a subgroup index.

https://www.youtube.com/watch?v=7CqXdSEN2k4

 

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

 

Highlighted
Super User IV
Super User IV

Re: Table filtering while import

@Tober1996 , if you are importing from the database , there is an advanced option where you can write a query and filter

 

AdvanceProperty.png

 

Or refer

https://www.youtube.com/watch?v=H0pd_YOKD0w



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Microsoft
Microsoft

Re: Table filtering while import

Hi @Tober1996 ,

 

Transform data before importing. In the queries editor,click the "group by" button then group by max process number of each order.

test_group_by.PNG

 

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

Highlighted
Frequent Visitor

Re: Table filtering while import

Hi, thanks to all for your solutions. I tried Lian's solution (it looked like the easiest -for me-). But my table has a lot of columns and after using lian's solution, there are only the two columns from the grouping. I would like that the other columns of the selected max line are still available. Is this possible?

Highlighted
Super User III
Super User III

Re: Table filtering while import

Hi @Tober1996 

Can you please share a sample of your input data so that we can see the structure of the table and  provide an example of what you need based on that data? Show what the result would be. Please paste the data here in text-tabular format so that it can be copied (instead of a screen cap)

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers 

SU18_powerbi_badge

Highlighted
Microsoft
Microsoft

Re: Table filtering while import

Hi @Tober1996 ,

 

Please try grouping like this:

test_group_by2.PNG

Add a step in the advanced editor after expanding the table:

Table.SelectRows(#"Expanded expand",each([Max]=[expand.flow_id]))

test_result.PNG

Sample .pbix

Best Regards,
Liang
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

Highlighted
Frequent Visitor

Re: Table filtering while import

Hi together, thank you very much! The advanced grouping ist the solution, but it is not needed to write the extra code to see the other columns. You only have to press the button with the two arrows in the headline of the new column. than all existings columns expand and are shown again. 😄 

View solution in original post

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors