Merging Queries using SharePoint lists

[Anonymous]

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...

[v-lili6-msft · Accepted solution]

HI, @Anonymous

     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

Result:

2. Use LOOKUPVALUE Function to add a state column

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

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:

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

[Anonymous]

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

Eric