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

Merging Queries using SharePoint lists

I have one excel sheet that is my sales information. I have another excel sheet that lists my discontinued items.

 

I am trying to add either a new column or measue to my sales information query based on the discontinued items list.

 

Here's some fake date. I am trying to put a Y or a 1 or some other designation in the discontinued item column of Excel 1 in the rows where the item number of Excel 1 is in the list of Excel 2  so I can use a slicer to filter my visualizations. I currently have a one to many relationship between the two item number columns.

 

https://hsek12inus-my.sharepoint.com/:i:/g/personal/eday_hse_k12_in_us/EbhKZhYdjplEjxExoD7y990B1poIg...

 

https://hsek12inus-my.sharepoint.com/:i:/g/personal/eday_hse_k12_in_us/Ef24XfIHFAlHnLBpeLGVddQB3ejfr...

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Merging Queries using SharePoint lists

HI, @ericlday

     After my research, you can do these as below:

Step1:

 you need to expand List in Edit Queries if you haven't unfolded it yet.

 

Reference : https://powerbi.microsoft.com/en-us/blog/how-to-expand-a-list-of-records-in-a-query/

 

Step2:

there are two simple ways to achieve your requirement

1.since you have created the relationship with them, then drag the field productID from table2 into page level filter or

report level filter then set filter show blank

16.PNG

Result:

17.PNG

2. Use LOOKUPVALUE Function to add a state column

State = IF(ISBLANK(LOOKUPVALUE(Table2[ProductID],Table2[ProductID],Table1[Product ID])),"Y","N")

18.PNG

then drag the field State into page level filter or report level filter then set filter show Y,

or drag the field State into a slicer and filter Y

Result:

19.PNG

here is pbix, and these two ways are shown in two pages, please try it.

https://www.dropbox.com/s/8jdxom5tcu91v1f/Merging%20Queries%20using%20SharePoint%20lists.pbix?dl=0

 

Best Regards,

Lin

 

 

 

 

 

 

 

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

Re: Merging Queries using SharePoint lists

HI, @ericlday

     After my research, you can do these as below:

Step1:

 you need to expand List in Edit Queries if you haven't unfolded it yet.

 

Reference : https://powerbi.microsoft.com/en-us/blog/how-to-expand-a-list-of-records-in-a-query/

 

Step2:

there are two simple ways to achieve your requirement

1.since you have created the relationship with them, then drag the field productID from table2 into page level filter or

report level filter then set filter show blank

16.PNG

Result:

17.PNG

2. Use LOOKUPVALUE Function to add a state column

State = IF(ISBLANK(LOOKUPVALUE(Table2[ProductID],Table2[ProductID],Table1[Product ID])),"Y","N")

18.PNG

then drag the field State into page level filter or report level filter then set filter show Y,

or drag the field State into a slicer and filter Y

Result:

19.PNG

here is pbix, and these two ways are shown in two pages, please try it.

https://www.dropbox.com/s/8jdxom5tcu91v1f/Merging%20Queries%20using%20SharePoint%20lists.pbix?dl=0

 

Best Regards,

Lin

 

 

 

 

 

 

 

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

Re: Merging Queries using SharePoint lists

Thank you! The second solution (LOOKUPVALUE) worked best for me.

 

Eric