Forum Discussion
Custom column by using two different tables
Hi.
I am trying to filter a sales table by the customer ID. I have a list of customers in a list to use check if they are contained in the customers iD column, those customers are in a particular category. So I want to filter the sales table with those costumers whose iD is in the list and the one category they belong. I tried creating a custom column but it takes forever to load. The customer list has 8000 rows.
with the custom column I had plan to filter it by selecting the value that satisfies all conditions. Any help or alternative to go about this please ?
Hi Sagejah9 ,
You may consider using Merge query. Suppose we have a sales table like this:
and a lookup table, you can convert back to table with list:
Merge Queries as New:
Expand table:
remove empty and unwanted columns:
The PBIX file is attached for reference.
Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
3 Replies
Sagejah9 ,The information you have provided is not making the problem clear to me. Can you please explain with an example.
Appreciate your Kudos.amitchandak
Basically I want to filter a table using a column from another table which I have converted to a list first. when I used the List.contains function inside the selectrows function, it seems to increase the size of the dataset instead.the m code looks like this
=Table.SelectRows(#"Change Type", each (List.contains(customer list,[customer])=true))the list has 8000 rows and the sales table with customer number column has 134000 rows
I am trying to use the list customer IDs to filter the sales table that has customer id as well.
Hi Sagejah9 ,
You may consider using Merge query. Suppose we have a sales table like this:
and a lookup table, you can convert back to table with list:
Merge Queries as New:
Expand table:
remove empty and unwanted columns:
The PBIX file is attached for reference.
Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
