Filter one Power BI Query based on an Excel Table

Question

I have a table called "_Vendor_List" in a spreadsheet and I use this to filter a query in that spreadsheet which I use for analytics.

Similarly, I would like to filter the same query in Power BI based on this same table. This works in excel, but I'm not sure if/how the syntax varies in Power BI for the Query.

My Excel Power Query Code is:

let
    Source = Sql.Database("MyDatabase", "MY_DATA"),
    ExcelTable = Excel.CurrentWorkbook(){[Name="_Vendor_List"]}[Content],
    #"PerformanceTable" = Source{[Schema="dbo",Item="PerformanceTable"]}[Data],
    Filter = Table.SelectRows(#"PerformanceTable", each List.Contains(ExcelTable[VENDOR], [VENDOR_CODE]))
in
    Filter

Now I'm not sure if CurrentWorkbook is usable in a power bi, so my question is how do I modify this to now filter the same query in Power BI using the same table (which will also be imported).

I guess the approach would be to first import _Vendor_List into Power BI, then either create a relationship or use DAX to filter measures based on this table — Nick.McDermaid
You can copy the excel power query into the power bi M query editor — Jon

Mark S. Mark S. · Accepted Answer · 2020-02-07T15:54:12

Okay, I figured this out as to what the syntax needs to be in order for this filter to work. I couldn't use CurrentWorkbook because the Power BI report isn't a workbook; however, you can refer to the Excel.Workbook as long as you reference the location.

The one area of improvement I'd like to do is refer to the workbook, not from it's location or drive (as that might change), but instead refer to it within the Power BI Report.

let
    Source = Sql.Database("MyDatabase", "MY_DATA"),
    #"PerformanceTable" = Source{[Schema="dbo",Item="PerformanceTable"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(#"PerformanceTable",{{"VENDOR_CODE", Int64.Type}}),
    Excel1 = Excel.Workbook(File.Contents("C:\Users\myaccount\OneDrive\Power BI\Vendor List.xlsx"), null, true),
    _Vendor_List_Table = Excel1{[Item="_Vendor_List",Kind="Table"]}[Data],
    ExcelChange = Table.TransformColumnTypes(_Vendor_List_Table,{{"Vendor", Int64.Type}, {"Name", type text}, {"Group", type text}}),
    Filter = Table.SelectRows(#"Changed Type", each List.Contains(ExcelChange[Vendor], [VENDOR_CODE]))
In
    Filter

Filter one Power BI Query based on an Excel Table

1 Answers