cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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.

View solution in original post

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.

View solution in original post

ericlday Frequent Visitor
Frequent Visitor

Re: Merging Queries using SharePoint lists

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

 

Eric

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: 351 members 3,256 guests
Please welcome our newest community members: